using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Xml;
using System.Globalization;
using System.IO;
using System.Xml.Schema;
using System.Xml.XPath;
using System.ServiceModel;
using System.ServiceModel.Channels;
using System.ServiceModel.Configuration;

using System.Configuration;
using System.Reflection;
using Microsoft.MSE.Catalog.Contracts;
using Microsoft.MSE;

namespace Microsoft.MSE.Catalog.Module
{
    public class DAL
    {
        private static NumberFormatInfo nfi = CultureInfo.CurrentCulture.NumberFormat;

        public static Guid GetIDFromName(string entityName, EntityClass entityClass, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            Guid entityID;
            const string sprocname = "[ServiceEngine].[GetIDFromName]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter entityNameParam = new SqlParameter("@EntityName", SqlDbType.NVarChar, 400);
                    SqlParameter entityClassParam = new SqlParameter("@EntityClass", SqlDbType.Int);
                    SqlParameter entityIDParam = new SqlParameter("@EntityID", SqlDbType.UniqueIdentifier);
                    entityIDParam.Direction = ParameterDirection.Output;

                    cmd.CommandType = CommandType.StoredProcedure;
                    entityNameParam.Value = entityName;
                    entityClassParam.Value = (int)entityClass;
                    cmd.Parameters.Add(entityNameParam);
                    cmd.Parameters.Add(entityClassParam);
                    cmd.Parameters.Add(entityIDParam);

                    cmd.ExecuteNonQuery();

                    if (entityIDParam.Value != DBNull.Value)
                    {
                        entityID = (Guid)entityIDParam.Value;
                    }
                    else
                    {
                        entityID = Guid.Empty;
                    }
                }
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return entityID;

        }

        public static EndpointList ListEndpointsForRuntimeServerID(Guid runtimeServerID, string hostName, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            EndpointList list = new EndpointList();
            const string sprocname = "[ServiceEngine].[ListEndpointsForRuntimeServerID]";
            const string sprocname2 = "[ServiceEngine].[GetRuntimeServer]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }
                // Get the hostname first
                if (hostName == null)
                {
                    using (SqlCommand cmd = new SqlCommand(sprocname2, conn, txn))
                    {
                        SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                        cmd.CommandType = CommandType.StoredProcedure;
                        IDParam.Value = runtimeServerID;
                        cmd.Parameters.Add(IDParam);

                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                reader.Read();
                                hostName = reader.IsDBNull(10) ? null : reader.GetString(10);                                
                            }
                        }
                    }
                }
                
                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter runtimeServerIDParam = new SqlParameter("@RuntimeServerID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    runtimeServerIDParam.Value = runtimeServerID;
                    cmd.Parameters.Add(runtimeServerIDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Endpoints = new List<EndpointListElement>();
                            while (reader.Read())
                            {
                                EndpointListElement element = new EndpointListElement();
                                element.ID = reader.GetGuid(0);
                                element.URI = reader.GetString(1);
                                element.Port = reader.GetInt32(2);
                                if (reader.IsDBNull(3))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(3);
                                }
                                list.Endpoints.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;

        }



        public static EndpointList ListEndpoints(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            EndpointList list = new EndpointList();
            const string sprocname = "[ServiceEngine].[ListEndpoints]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Endpoints = new List<EndpointListElement>();
                            while (reader.Read())
                            {
                                EndpointListElement element = new EndpointListElement();
                                element.ID = reader.GetGuid(0);
                                element.URI = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.Endpoints.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;

        }

        public static Endpoint GetEndpoint(Guid ID, Guid RuntimeServerID, bool provideWSDL, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            Endpoint endpoint = new Endpoint();            
            const string sprocname = "[ServiceEngine].[GetEndpoint]";
            const string sprocname2 = "[ServiceEngine].[GetRuntimeServer]";

            if (RuntimeServerID == Guid.Empty) provideWSDL = false;

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                


                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            endpoint.ID = reader.GetGuid(0);
                            endpoint.Path = reader.GetString(1);
                            endpoint.Binding = GetMSEBinding(reader.GetGuid(2), txn, conn);
                            if (reader.IsDBNull(3))
                            {
                                endpoint.Policy = null;
                            }
                            else
                            {
                                endpoint.Policy = GetPolicy(reader.GetGuid(3), txn, conn);
                            }
                            if (reader.IsDBNull(4))
                            {
                                endpoint.Description = null;
                            }
                            else
                            {
                                endpoint.Description = reader.GetString(4);
                            }
                            if (reader.IsDBNull(5))
                            {
                                endpoint.NamespaceURI = null;
                            }
                            else
                            {
                                endpoint.NamespaceURI = reader.GetString(5);
                            }
                            endpoint.TestCallsAccepted = reader.GetBoolean(6);
                            endpoint.AutoOperationPubUpdate = reader.GetBoolean(7);
                            endpoint.AutoOperationActivationUpdate = reader.GetBoolean(8);
                            if (reader.IsDBNull(9))
                            {
                                endpoint.Configuration = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(9).CreateReader());
                                endpoint.Configuration = xdoc.DocumentElement;
                            }
                            if (reader.IsDBNull(10))
                            {
                                endpoint.ServiceName = null;
                            }
                            else
                            {
                                endpoint.ServiceName = reader.GetString(10);
                            }
                            if (reader.IsDBNull(11))
                            {
                                endpoint.Port = 0;
                            }
                            else
                            {
                                endpoint.Port = reader.GetInt32(11);
                            }

                            endpoint.PubOpVersionsList = ListPublishedOperationsForEndpoint(ID, txn, conn);
                            endpoint.AssocOpVersionsList = ListAssociatedOperationsForEndpoint(ID, txn, conn);

                            Binding binding = BindingHelper.DeserializeBinding(endpoint.Binding.BindingConfig);
                            
                            // Provide WSDL only if demanded
                            if (provideWSDL)
                            {
                                String epHost = "";
                                using (SqlCommand cmd2 = new SqlCommand(sprocname2, conn, txn))
                                {
                                    SqlParameter IDParam2 = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                                    cmd2.CommandType = CommandType.StoredProcedure;
                                    IDParam2.Value = RuntimeServerID;
                                    cmd2.Parameters.Add(IDParam2);

                                    using (SqlDataReader reader2 = cmd2.ExecuteReader())
                                    {
                                        if (reader2.HasRows)
                                        {
                                            reader2.Read();
                                            epHost += reader2.IsDBNull(10) ? null : reader2.GetString(10);
                                        }
                                    }
                                }
                                if (endpoint.Port != 0)
                                    epHost += ":" + Convert.ToString(endpoint.Port);

                                endpoint.Address = epHost + @"/" + endpoint.Path;

                                endpoint.WSDL = GenerateWSDLForEndpoint(endpoint, endpoint.Address, txn, conn);
                            }
                            else
                            {
                                // Just set the address to the path to maintain compatibility
                                endpoint.Address = endpoint.Path;
                            }

                            endpoint.UddiAssociations = ListEndpointUddiAssociation(ID, txn, conn);
                        }
                    }
                }

            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return endpoint;

        }

        public static OperationVersionList ListAssociatedOperationsForEndpoint(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            OperationVersionList list = new OperationVersionList();
            const string sprocname = "[ServiceEngine].[ListAssociatedOperationsForEndpoint]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.OperationVersions = new List<OperationVersionListElement>();
                            while (reader.Read())
                            {
                                OperationVersionListElement element = new OperationVersionListElement();
                                element.ID = reader.GetGuid(0);
                                element.OperationName = reader.GetString(1);
                                VersionNumber version = new VersionNumber();
                                version.Major = reader.GetInt32(2).ToString();
                                version.Minor = reader.GetInt32(3).ToString();
                                version.Build = reader.GetInt32(4).ToString();
                                version.Qfe = reader.GetInt32(5).ToString();
                                element.Version = version;
                                list.OperationVersions.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;

        }


        public static OperationVersionList ListPublishedOperationsForEndpoint(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            OperationVersionList list = new OperationVersionList();

            const string sprocname = "[ServiceEngine].[ListPublishedOperationsForEndpoint]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.OperationVersions = new List<OperationVersionListElement>();
                            while (reader.Read())
                            {
                                OperationVersionListElement element = new OperationVersionListElement();
                                element.ID = reader.GetGuid(0);
                                element.OperationName = reader.GetString(1);
                                VersionNumber version = new VersionNumber();
                                version.Major = reader.GetInt32(2).ToString();
                                version.Minor = reader.GetInt32(3).ToString();
                                version.Build = reader.GetInt32(4).ToString();
                                version.Qfe = reader.GetInt32(5).ToString();
                                element.Version = version;
                                list.OperationVersions.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;

        }

        public static List<EndpointAssociatedUddi> ListEndpointUddiAssociation(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[ListEndpointUddiAssociation]";
            List<EndpointAssociatedUddi> uddiAssociations = new List<EndpointAssociatedUddi>();

            try
            {

                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            EndpointAssociatedUddi endpointAssociatedUddi = new EndpointAssociatedUddi();
                            endpointAssociatedUddi.UddiAssociationID = reader.GetGuid(0);
                            endpointAssociatedUddi.EndpointID = reader.GetGuid(1);
                            endpointAssociatedUddi.TModelKey = reader.GetString(2);
                            endpointAssociatedUddi.ServiceKey = reader.GetString(3);
                            endpointAssociatedUddi.BindingKey = reader.GetString(4);
                            endpointAssociatedUddi.WsdlUrl = reader.GetString(5);
                            endpointAssociatedUddi.BindingUrl = reader.GetString(6);
                            endpointAssociatedUddi.RuntimeServerID = reader.GetGuid(7);
                            uddiAssociations.Add(endpointAssociatedUddi);
                        }
                    }
                }
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return uddiAssociations;

        }




        public static Policy GetPolicy(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            Policy policy = new Policy();
            const string sprocname = "[ServiceEngine].[GetPolicy]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            policy.ID = reader.GetGuid(0);
                            policy.Name = reader.GetString(1);
                            XmlDocument xdoc = new XmlDocument();
                            xdoc.Load(reader.GetSqlXml(2).CreateReader());
                            policy.WSPolicy = xdoc.DocumentElement;
                            if (reader.IsDBNull(3))
                            {
                                policy.Description = null;
                            }
                            else
                            {
                                policy.Description = reader.GetString(3);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return policy;

        }

        public static MSEBinding GetMSEBinding(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            MSEBinding binding = new MSEBinding();
            const string sprocname = "[ServiceEngine].[GetBinding]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            binding.ID = reader.GetGuid(0);
                            binding.Name = reader.GetString(1);
                            XmlDocument xdoc = new XmlDocument();
                            xdoc.Load(reader.GetSqlXml(2).CreateReader());
                            binding.BindingConfig = xdoc.DocumentElement;
                            if (reader.IsDBNull(3))
                            {
                                binding.Description = null;
                            }
                            else
                            {
                                binding.Description = reader.GetString(3);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return binding;

        }

        public static void DeleteEndpoint(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteEndpoint]";
            const string sprocname2 = "[ServiceEngine].[ListRuntimeServersForEndpointID]";

            List<Guid> runtimeServerIDs = new List<Guid>();
            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                // Get all the Runtimeservers ssociated with this EndPoint
                using (SqlCommand cmd = new SqlCommand(sprocname2, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                runtimeServerIDs.Add(reader.GetGuid(0));
                            }
                        }
                    }
                }

                // Remove any UDDI associations 
                if (runtimeServerIDs.Count > 0)
                {
                    foreach(Guid runtimeServerID in runtimeServerIDs)
                    {
                        RemoveEndPointFromUddi(ID, runtimeServerID, txn, conn);
                    }
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        public static Endpoint UpdateEndpoint(Endpoint endpoint, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateEndpoint]";
            const string sprocname2 = "[ServiceEngine].[ListRuntimeServersForEndpointID]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                if (txn == null) txn = conn.BeginTransaction(IsolationLevel.Snapshot);

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter PortParam = new SqlParameter("@Port", SqlDbType.Int);
                    SqlParameter BindingIDParam = new SqlParameter("@BindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);
                    SqlParameter NamespaceURIParam = new SqlParameter("@NamespaceURI", SqlDbType.NText);
                    SqlParameter ServiceNameParam = new SqlParameter("@ServiceName", SqlDbType.NText);
                    SqlParameter ConfigurationParam = new SqlParameter("@Configuration", SqlDbType.Xml);
                    SqlParameter TestCallsAcceptedParam = new SqlParameter("@TestCallsAccepted", SqlDbType.Bit);
                    SqlParameter AutoOperationPubUpdateParam = new SqlParameter("@AutoOperationPubUpdate", SqlDbType.Bit);
                    SqlParameter AutoOperationActivationUpdateParam = new SqlParameter("@AutoOperationActivationUpdate", SqlDbType.Bit);


                    if (endpoint.Policy != null)
                    {
                        PolicyIDParam.Value = endpoint.Policy.ID;
                    }
                    else
                    {
                        PolicyIDParam.Value = DBNull.Value;
                    }

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = endpoint.ID;
                    NameParam.Value = endpoint.Path;
                    if (endpoint.Binding != null)
                    {
                        if (endpoint.Binding.ID.Equals(Guid.Empty))
                        {
                            BindingIDParam.Value = CreateMSEBinding(endpoint.Binding, txn, conn);
                        }
                        else
                        {
                            BindingIDParam.Value = endpoint.Binding.ID;
                        }
                    }
                    else
                    {
                        throw new ApplicationException("UpdateEndpoint requires Binding, but no Binding was supplied");
                    }
                    PortParam.Value = endpoint.Port;
                    DescriptionParam.Value = endpoint.Description;
                    NamespaceURIParam.Value = endpoint.NamespaceURI;
                    ServiceNameParam.Value = endpoint.ServiceName;
                    TestCallsAcceptedParam.Value = endpoint.TestCallsAccepted;
                    AutoOperationPubUpdateParam.Value = endpoint.AutoOperationPubUpdate;
                    AutoOperationActivationUpdateParam.Value = endpoint.AutoOperationActivationUpdate;
                    if ((endpoint.Configuration == null) || (string.IsNullOrEmpty(endpoint.Configuration.OuterXml)))
                    {
                        ConfigurationParam.Value = DBNull.Value;
                    }
                    else
                    {
                        ConfigurationParam.Value = endpoint.Configuration.OuterXml;
                    }
                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(PortParam);
                    cmd.Parameters.Add(BindingIDParam);
                    cmd.Parameters.Add(PolicyIDParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(NamespaceURIParam);
                    cmd.Parameters.Add(ConfigurationParam);
                    cmd.Parameters.Add(ServiceNameParam);
                    cmd.Parameters.Add(TestCallsAcceptedParam);
                    cmd.Parameters.Add(AutoOperationPubUpdateParam);
                    cmd.Parameters.Add(AutoOperationActivationUpdateParam);

                    cmd.ExecuteNonQuery();
                }

                // Get all the Runtimeservers ssociated with this EndPoint
                List<Guid> runtimeServerIDs = new List<Guid>();
                using (SqlCommand cmd = new SqlCommand(sprocname2, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = endpoint.ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                runtimeServerIDs.Add(reader.GetGuid(0));
                            }
                        }
                    }
                }

                // Update any UDDI associations 
                if (runtimeServerIDs.Count > 0)
                {
                    foreach (Guid runtimeServerID in runtimeServerIDs)
                    {
                        PublishEndPointToUddi(endpoint.ID, runtimeServerID,false, txn, conn);
                    }
                }


                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return endpoint;
        }

        public static Guid CreateEndpoint(Endpoint endpoint, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateEndpoint]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                if (txn == null) txn = conn.BeginTransaction(IsolationLevel.Snapshot);

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter PortParam = new SqlParameter("@Port", SqlDbType.Int);
                    SqlParameter BindingIDParam = new SqlParameter("@BindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter NamespaceURIParam = new SqlParameter("@NamespaceURI", SqlDbType.NText);
                    SqlParameter ServiceNameParam = new SqlParameter("@ServiceName", SqlDbType.NText);
                    SqlParameter ConfigurationParam = new SqlParameter("@Configuration", SqlDbType.Xml);
                    SqlParameter TestCallsAcceptedParam = new SqlParameter("@TestCallsAccepted", SqlDbType.Bit);
                    SqlParameter AutoOperationPubUpdateParam = new SqlParameter("@AutoOperationPubUpdate", SqlDbType.Bit);
                    SqlParameter AutoOperationActivationUpdateParam = new SqlParameter("@AutoOperationActivationUpdate", SqlDbType.Bit);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    // Remove Uri Scheme
                    NameParam.Value = RemoveUriScheme(endpoint.Path);
                    if (endpoint.Policy != null)
                    {
                        PolicyIDParam.Value = endpoint.Policy.ID;
                    }
                    else
                    {
                        PolicyIDParam.Value = DBNull.Value;
                    }
                    if (endpoint.Binding != null)
                    {
                        if (endpoint.Binding.ID.Equals(Guid.Empty))
                        {
                            BindingIDParam.Value = CreateMSEBinding(endpoint.Binding, txn, conn);
                        }
                        else
                        {
                            BindingIDParam.Value = endpoint.Binding.ID;
                        }
                    }
                    else
                    {
                        throw new ApplicationException("CreateEndpoint requires Binding, but no Binding was supplied");
                    }
                    DescriptionParam.Value = endpoint.Description;
                    PortParam.Value = endpoint.Port;
                    NamespaceURIParam.Value = endpoint.NamespaceURI;
                    ServiceNameParam.Value = endpoint.ServiceName;
                    TestCallsAcceptedParam.Value = endpoint.TestCallsAccepted;
                    AutoOperationPubUpdateParam.Value = endpoint.AutoOperationPubUpdate;
                    AutoOperationActivationUpdateParam.Value = endpoint.AutoOperationActivationUpdate;
                    if ((endpoint.Configuration == null) || (string.IsNullOrEmpty(endpoint.Configuration.OuterXml)))
                    {
                        ConfigurationParam.Value = DBNull.Value;
                    }
                    else
                    {
                        ConfigurationParam.Value = endpoint.Configuration.OuterXml;
                    }
                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(PortParam);
                    cmd.Parameters.Add(BindingIDParam);
                    cmd.Parameters.Add(PolicyIDParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(NamespaceURIParam);
                    cmd.Parameters.Add(ConfigurationParam);
                    cmd.Parameters.Add(ServiceNameParam);
                    cmd.Parameters.Add(TestCallsAcceptedParam);
                    cmd.Parameters.Add(AutoOperationPubUpdateParam);
                    cmd.Parameters.Add(AutoOperationActivationUpdateParam);

                    cmd.ExecuteNonQuery();

                    ID = (Guid)IDParam.Value;
                }
                if ((autoclose) && (txn != null)) txn.Commit();

            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return ID;
        }

        public static RuntimeServerList ListRuntimeServers(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            RuntimeServerList list = new RuntimeServerList();
            const string sprocname = "[ServiceEngine].[ListRuntimeServers]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.RuntimeServers = new List<RuntimeServerListElement>();
                            while (reader.Read())
                            {
                                RuntimeServerListElement element = new RuntimeServerListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.RuntimeServers.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;

        }

        public static ChannelList ListChannels(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            ChannelList list = new ChannelList();
            const string sprocname = "[ServiceEngine].[ListChannels]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Channels = new List<ChannelListElement>();
                            while (reader.Read())
                            {
                                ChannelListElement element = new ChannelListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.Channels.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;

        }

        public static RuntimeServer GetRuntimeServer(Guid ID, bool provideWSDL, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            RuntimeServer runtimeServer = new RuntimeServer();
            const string sprocname = "[ServiceEngine].[GetRuntimeServer]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            runtimeServer.ID = reader.GetGuid(0);
                            runtimeServer.Name = reader.GetString(1);

                            runtimeServer.Name = reader.IsDBNull(1) ? null : reader.GetString(1);

                            runtimeServer.CatalogURI = reader.IsDBNull(2) ? null : reader.GetString(2);
                            runtimeServer.CatalogBinding = reader.IsDBNull(3) ? null : GetMSEBinding(reader.GetGuid(3), txn, conn);

                            runtimeServer.NotificationURI = reader.IsDBNull(4) ? null : reader.GetString(4);
                            runtimeServer.NotificationBinding = reader.IsDBNull(5) ? null : GetMSEBinding(reader.GetGuid(5), txn, conn);

                            runtimeServer.BrokerURI = reader.IsDBNull(6) ? null : reader.GetString(6);
                            runtimeServer.BrokerBinding = reader.IsDBNull(7) ? null : GetMSEBinding(reader.GetGuid(7), txn, conn);

                            runtimeServer.ListenerURI = reader.IsDBNull(8) ? null : reader.GetString(8);
                            runtimeServer.ListenerBinding = reader.IsDBNull(9) ? null : GetMSEBinding(reader.GetGuid(9), txn, conn);

                            runtimeServer.HostName = reader.IsDBNull(10) ? null : reader.GetString(10);
                            runtimeServer.Description = reader.IsDBNull(11) ? null : reader.GetString(11);

                            runtimeServer.Endpoints = new List<Endpoint>();
                            EndpointList endpointList = ListEndpointsForRuntimeServerID(ID, runtimeServer.HostName, txn, conn);
                            if (endpointList.Endpoints != null)
                            {
                                foreach (EndpointListElement element in endpointList.Endpoints)
                                {
                                    runtimeServer.Endpoints.Add(GetEndpoint(element.ID, runtimeServer.ID, provideWSDL, txn, conn));
                                }
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return runtimeServer;

        }

        public static void DeleteRuntimeServer(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteRuntimeServer]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }
                if ((autoclose) && (txn != null)) txn.Commit();

            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        public static void DeleteChannel(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteChannel]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

                //Delete all entity Policies SGK
                DeletePoliciesFromEntity(ID, txn, conn);

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        public static RuntimeServer UpdateRuntimeServer(RuntimeServer runtimeServer, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateRuntimeServer]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter CatalogURIParam = new SqlParameter("@CatalogURI", SqlDbType.NVarChar, 400);
                    SqlParameter CatalogBindingParam = new SqlParameter("@CatalogBindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter NotificationURIParam = new SqlParameter("@NotificationURI", SqlDbType.NVarChar, 400);
                    SqlParameter NotificationBindingParam = new SqlParameter("@NotificationBindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter HostNameParam = new SqlParameter("@HostName", SqlDbType.NVarChar, 400);
                    SqlParameter BrokerURIParam = new SqlParameter("@BrokerURI", SqlDbType.NVarChar, 400);
                    SqlParameter BrokerBindingParam = new SqlParameter("@BrokerBindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter ListenerParam = new SqlParameter("@ListenerURI", SqlDbType.NVarChar, 400);
                    SqlParameter ListenerBindingParam = new SqlParameter("@ListenerBindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = runtimeServer.ID;
                    NameParam.Value = runtimeServer.Name;

                    if (string.IsNullOrEmpty(runtimeServer.CatalogURI) == false)
                    {
                        CatalogURIParam.Value = runtimeServer.CatalogURI;
                        CatalogBindingParam.Value = runtimeServer.CatalogBinding.ID;
                    }
                    else
                    {
                        CatalogURIParam.Value = null;
                        CatalogBindingParam.Value = null;
                    }

                    if (string.IsNullOrEmpty(runtimeServer.NotificationURI) == false)
                    {
                        NotificationURIParam.Value = runtimeServer.NotificationURI;
                        NotificationBindingParam.Value = runtimeServer.NotificationBinding.ID;
                    }
                    else
                    {
                        NotificationURIParam.Value = null;
                        NotificationBindingParam.Value = null;
                    }

                    if (string.IsNullOrEmpty(runtimeServer.BrokerURI) == false)
                    {
                        BrokerURIParam.Value = runtimeServer.BrokerURI;
                        BrokerBindingParam.Value = runtimeServer.BrokerBinding.ID;
                    }
                    else
                    {
                        BrokerURIParam.Value = null;
                        BrokerBindingParam.Value = null;
                    }

                    if (string.IsNullOrEmpty(runtimeServer.ListenerURI) == false)
                    {
                        ListenerParam.Value = runtimeServer.ListenerURI;
                        ListenerBindingParam.Value = runtimeServer.ListenerBinding.ID;
                    }
                    else
                    {
                        ListenerParam.Value = null;
                        ListenerBindingParam.Value = null;
                    }

                    if (string.IsNullOrEmpty(runtimeServer.HostName) == false)
                    {
                        HostNameParam.Value = runtimeServer.HostName;
                    }
                    else
                    {
                        HostNameParam.Value = null;
                    }

                    DescriptionParam.Value = runtimeServer.Description;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(HostNameParam);
                    cmd.Parameters.Add(CatalogURIParam);
                    cmd.Parameters.Add(NotificationURIParam);
                    cmd.Parameters.Add(BrokerURIParam);
                    cmd.Parameters.Add(ListenerParam);
                    cmd.Parameters.Add(CatalogBindingParam);
                    cmd.Parameters.Add(NotificationBindingParam);
                    cmd.Parameters.Add(BrokerBindingParam);
                    cmd.Parameters.Add(ListenerBindingParam);
                    cmd.Parameters.Add(DescriptionParam);

                    cmd.ExecuteNonQuery();
                }
                #region new update
                EndpointList list = ListEndpointsForRuntimeServerID(runtimeServer.ID,runtimeServer.HostName, txn, conn);

                //remove any endpoint associations that should no longer be
                bool present = false;
                if (list.Endpoints != null)
                {
                    foreach (EndpointListElement ep in list.Endpoints)
                    {
                        present = false;
                        if (runtimeServer.Endpoints != null)
                        {
                            foreach (Endpoint xep in runtimeServer.Endpoints)
                            {
                                if (ep.ID == xep.ID)
                                    present = true;
                            }
                        }
                        if (!present)
                            RemoveEndpointFromRuntimeServer(ep.ID, runtimeServer.ID, txn, conn);
                    }
                }
                //Add any new endpoint associations
                if (runtimeServer.Endpoints != null)
                {
                    foreach (Endpoint xep in runtimeServer.Endpoints)
                    {
                        present = false;
                        if (list.Endpoints != null)
                        {
                            foreach (EndpointListElement ep in list.Endpoints)
                            {
                                if (ep.ID == xep.ID)
                                {
                                    present = true;
                                    break;
                                }
                            }
                        }
                        if (!present)
                        {
                            if (xep.ID.Equals(Guid.Empty))
                            {
                                xep.ID = CreateEndpoint(xep, txn, conn);
                            }
                            AssignEndpointToRuntimeServer(xep.ID, runtimeServer.ID, txn, conn);
                        }
                    }
                }
                #endregion

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return runtimeServer;
        }

        public static Guid CreateRuntimeServer(RuntimeServer runtimeServer, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateRuntimeServer]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter CatalogURIParam = new SqlParameter("@CatalogURI", SqlDbType.NVarChar, 400);
                    SqlParameter CatalogBindingParam = new SqlParameter("@CatalogBindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter NotificationURIParam = new SqlParameter("@NotificationURI", SqlDbType.NVarChar, 400);
                    SqlParameter NotificationBindingParam = new SqlParameter("@NotificationBindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter HostNameParam = new SqlParameter("@HostName", SqlDbType.NVarChar, 400);
                    SqlParameter BrokerURIParam = new SqlParameter("@BrokerURI", SqlDbType.NVarChar, 400);
                    SqlParameter BrokerBindingParam = new SqlParameter("@BrokerBindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter ListenerParam = new SqlParameter("@ListenerURI", SqlDbType.NVarChar, 400);
                    SqlParameter ListenerBindingParam = new SqlParameter("@ListenerBindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    NameParam.Value = runtimeServer.Name;

                    if (string.IsNullOrEmpty(runtimeServer.CatalogURI) == false)
                    {
                        CatalogURIParam.Value = runtimeServer.CatalogURI;
                        CatalogBindingParam.Value = runtimeServer.CatalogBinding.ID;
                    }
                    else
                    {
                        CatalogURIParam.Value = null;
                        CatalogBindingParam.Value = null;
                    }

                    if (string.IsNullOrEmpty(runtimeServer.NotificationURI) == false)
                    {
                        NotificationURIParam.Value = runtimeServer.NotificationURI;
                        NotificationBindingParam.Value = runtimeServer.NotificationBinding.ID;
                    }
                    else
                    {
                        NotificationURIParam.Value = null;
                        NotificationBindingParam.Value = null;
                    }

                    if (string.IsNullOrEmpty(runtimeServer.BrokerURI) == false)
                    {
                        BrokerURIParam.Value = runtimeServer.BrokerURI;
                        BrokerBindingParam.Value = runtimeServer.BrokerBinding.ID;
                    }
                    else
                    {
                        BrokerURIParam.Value = null;
                        BrokerBindingParam.Value = null;
                    }

                    if (string.IsNullOrEmpty(runtimeServer.ListenerURI) == false)
                    {
                        ListenerParam.Value = runtimeServer.ListenerURI;
                        ListenerBindingParam.Value = runtimeServer.ListenerBinding.ID;
                    }
                    else
                    {
                        ListenerParam.Value = null;
                        ListenerBindingParam.Value = null;
                    }

                    if (string.IsNullOrEmpty(runtimeServer.HostName) == false)
                    {
                        HostNameParam.Value = runtimeServer.HostName;                        
                    }
                    else
                    {
                        HostNameParam.Value = null;                       
                    }

                    DescriptionParam.Value = runtimeServer.Description;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(CatalogURIParam);
                    cmd.Parameters.Add(NotificationURIParam);
                    cmd.Parameters.Add(HostNameParam);
                    cmd.Parameters.Add(BrokerURIParam);
                    cmd.Parameters.Add(ListenerParam);
                    cmd.Parameters.Add(CatalogBindingParam);
                    cmd.Parameters.Add(NotificationBindingParam);
                    cmd.Parameters.Add(BrokerBindingParam);
                    cmd.Parameters.Add(ListenerBindingParam);
                    cmd.Parameters.Add(DescriptionParam);

                    cmd.ExecuteNonQuery();

                    ID = (Guid)IDParam.Value;
                }
                if ((autoclose) && (txn != null)) txn.Commit();

            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return ID;
        }

        public static Guid CreateChannel(Channel chnl, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateChannel]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter MonikerSchemaParam = new SqlParameter("@MonikerSchema", SqlDbType.Xml);
                    SqlParameter BindingIDParam = new SqlParameter("@BindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter EditorAssemblyParam = new SqlParameter("@EditorAssembly", SqlDbType.NVarChar, 4000);
                    SqlParameter EditorTypeParam = new SqlParameter("@EditorTypeName", SqlDbType.NVarChar, 4000);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    NameParam.Value = chnl.Name;
                    if (chnl.MonikerSchema != null)
                    {
                        MonikerSchemaParam.Value = chnl.MonikerSchema.OuterXml;
                    }
                    else
                    {
                        throw new ApplicationException("CreateChannel failed: MonikerSchema is a required element but was not sent");
                    }
                    BindingIDParam.Value = chnl.Binding.ID;
                    EditorAssemblyParam.Value = chnl.MonikerEditorAssemblyName;
                    EditorTypeParam.Value = chnl.MonikerEditorTypeName;
                    DescriptionParam.Value = chnl.Description;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(MonikerSchemaParam);
                    cmd.Parameters.Add(BindingIDParam);
                    cmd.Parameters.Add(EditorAssemblyParam);
                    cmd.Parameters.Add(EditorTypeParam);
                    cmd.Parameters.Add(DescriptionParam);

                    cmd.ExecuteNonQuery();

                    ID = (Guid)IDParam.Value;
                    //Assign Policies SGK
                    if (chnl.Policies != null)
                    {
                        foreach (Policy pol in chnl.Policies)
                        {
                            AssignEntityPolicy(ID, pol.ID, txn, conn);
                        }
                    }
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return ID;
        }

        public static Channel UpdateChannel(Channel chnl, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateChannel]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter MonikerSchemaParam = new SqlParameter("@MonikerSchema", SqlDbType.Xml);
                    SqlParameter BindingIDParam = new SqlParameter("@BindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter EditorAssemblyParam = new SqlParameter("@EditorAssembly", SqlDbType.NVarChar, 4000);
                    SqlParameter EditorTypeParam = new SqlParameter("@EditorTypeName", SqlDbType.NVarChar, 4000);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = chnl.ID;
                    NameParam.Value = chnl.Name;
                    if (chnl.MonikerSchema != null)
                    {
                        MonikerSchemaParam.Value = chnl.MonikerSchema.OuterXml;
                    }
                    else
                    {
                        throw new ApplicationException("UpdateChannel failed: MonikerSchema is a required element but was not sent");
                    }
                    BindingIDParam.Value = chnl.Binding.ID;
                    EditorAssemblyParam.Value = chnl.MonikerEditorAssemblyName;
                    EditorTypeParam.Value = chnl.MonikerEditorTypeName;
                    DescriptionParam.Value = chnl.Description;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(MonikerSchemaParam);
                    cmd.Parameters.Add(BindingIDParam);
                    cmd.Parameters.Add(EditorAssemblyParam);
                    cmd.Parameters.Add(EditorTypeParam);
                    cmd.Parameters.Add(DescriptionParam);

                    cmd.ExecuteNonQuery();

                    ID = (Guid)IDParam.Value;
                    //Assign Policies SGK
                    DeletePoliciesFromEntity(chnl.ID, txn, conn);

                    if (chnl.Policies != null)
                    {
                        foreach (Policy pol in chnl.Policies)
                        {
                            AssignEntityPolicy(chnl.ID, pol.ID, txn, conn);
                        }
                    }
                }
                if ((autoclose) && (txn != null)) txn.Commit();

            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return chnl;
        }

        public static MessengerList ListMessengers(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            MessengerList list = new MessengerList();
            const string sprocname = "[ServiceEngine].[ListMessengers]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Messengers = new List<MessengerListElement>();
                            while (reader.Read())
                            {
                                MessengerListElement element = new MessengerListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.Messengers.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;

        }
      
        public static void AssignEndpointToRuntimeServer(Guid endpointID, Guid runtimeServerID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[AssignEndpointToRuntimeServer]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter RuntimeServerIDParam = new SqlParameter("@RuntimeServerID", SqlDbType.UniqueIdentifier);
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    RuntimeServerIDParam.Value = runtimeServerID;
                    cmd.Parameters.Add(RuntimeServerIDParam);
                    EndpointIDParam.Value = endpointID;
                    cmd.Parameters.Add(EndpointIDParam);

                    cmd.ExecuteNonQuery();
                }

                // Time to publish Endpoint to UDDI 
                PublishEndPointToUddi(endpointID, runtimeServerID, false, txn, conn);

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        private static Binding GetBindingFromBindingCollectionElement(
                BindingCollectionElement bindingCollectionElement,
                BindingsSection bSec)
        {
            Binding newBinding = null;
            //BindingElementCollection elements = null;
            bool isCustomBinding = false;
            try
            {
                if (null != bindingCollectionElement)
                {
                    if (bindingCollectionElement.BindingType == typeof(BasicHttpBinding))
                        newBinding = new BasicHttpBinding();
                    else if (bindingCollectionElement.BindingType == typeof(CustomBinding))
                    {
                        newBinding = new CustomBinding();
                        isCustomBinding = true;
                    }
                    else if (bindingCollectionElement.BindingType == typeof(NetMsmqBinding))
                        newBinding = new NetMsmqBinding();
                    else if (bindingCollectionElement.BindingType == typeof(NetNamedPipeBinding))
                        newBinding = new NetNamedPipeBinding();
                    else if (bindingCollectionElement.BindingType == typeof(NetPeerTcpBinding))
                        newBinding = new NetPeerTcpBinding();
                    else if (bindingCollectionElement.BindingType == typeof(WSDualHttpBinding))
                        newBinding = new WSDualHttpBinding();
                    else if (bindingCollectionElement.BindingType == typeof(WSHttpBinding))
                        newBinding = new WSHttpBinding();
                    else if (bindingCollectionElement.BindingType == typeof(NetTcpBinding))
                        newBinding = new NetTcpBinding();

                    //Should we delete all default bindings?
                    //ApplyConfiguration might alreadey do just that
                    //elements = newBinding.CreateBindingElements();
                    if (isCustomBinding)
                    {
                        CustomBindingCollectionElement cbce = bSec.CustomBinding;

                        Type bExeType = typeof(BindingElementExtensionElement);
                        MethodInfo bExeMi = bExeType.GetMethod("CreateBindingElement",
                                                BindingFlags.Instance | BindingFlags.NonPublic);

                        foreach (CustomBindingElement custBe in cbce.Bindings)
                        {
                            string strBe = custBe.Name;
                            foreach (BindingElementExtensionElement bExe in custBe)
                            {

                                ((CustomBinding)newBinding).Elements.Add(
                                            (BindingElement)bExeMi.Invoke(bExe, null));
                            }
                        }
                    }
                    else
                    {
                        foreach (IBindingConfigurationElement bEleEx in bindingCollectionElement.ConfiguredBindings)
                        {
                            bEleEx.ApplyConfiguration(newBinding);
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                //TraceHelper.TraceError("Error in GetBindingFromBindingCollectionElement. Exception: " + ex.Message);
            }
            return newBinding;
        }

        public static void RemoveEndpointFromRuntimeServer(Guid endpointID, Guid runtimeServerID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemoveEndpointFromRuntimeServer]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter RuntimeServerIDParam = new SqlParameter("@RuntimeServerID", SqlDbType.UniqueIdentifier);
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    RuntimeServerIDParam.Value = runtimeServerID;
                    cmd.Parameters.Add(RuntimeServerIDParam);
                    EndpointIDParam.Value = endpointID;
                    cmd.Parameters.Add(EndpointIDParam);

                    cmd.ExecuteNonQuery();
                }

                // Time to remove Endpoint to UDDI 
                RemoveEndPointFromUddi(endpointID, runtimeServerID, txn, conn);

                if ((autoclose) && (txn != null)) txn.Commit();

            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        public static void RemoveEndpointsFromRuntimeServer(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemoveEndpointsFromRuntimeServer]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }
                if ((autoclose) && (txn != null)) txn.Commit();

            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        public static Uri GetEndpointServiceUri(Endpoint endpoint, RuntimeServer runtimeServer)
        {
            Uri serviceUri;

            Binding myBinding = BindingHelper.DeserializeBinding(endpoint.Binding.BindingConfig);
            try
            {
                string uri = "";
                if (endpoint.Port > 0)
                    uri = string.Format("{0}://{1}:{2}/{3}", myBinding.Scheme,runtimeServer.HostName, endpoint.Port, endpoint.Path);
                else
                    uri = string.Format("{0}://{1}/{2}", myBinding.Scheme, runtimeServer.HostName, endpoint.Path);
                serviceUri = new Uri(uri);
            }
            catch { serviceUri = null; }

            return serviceUri;
        }

        public static XmlElement GenerateWSDLForEndpoint(Endpoint endpoint, string epHost, SqlTransaction txn, SqlConnection conn)
        {
            XmlDocument response = new XmlDocument();
            XmlWriterSettings writerSettings = new XmlWriterSettings();
            writerSettings.ConformanceLevel = ConformanceLevel.Auto;
            writerSettings.OmitXmlDeclaration = true;
            StringBuilder buffer = new StringBuilder();
            string virtualServiceName = null;
            string virtualServiceNS = null;
            Dictionary<string, string> prefixes = new Dictionary<string, string>();
            int prefixIdx = 0;
            StringBuilder rawschemas = new StringBuilder();
            Uri serviceUri;

            //get list of published endpoints into an operationversions list
            OperationVersionList publishedOpVers = DAL.ListPublishedOperationsForEndpoint(endpoint.ID, null, null);

            if ((((endpoint == null) || publishedOpVers == null) || publishedOpVers.OperationVersions == null))
                return null;

            if (publishedOpVers.OperationVersions.Count < 1)
                return null;
         
            //if (services.Count > 1) {
            //rcamacho 8/14 - Commented this out
            Binding myBinding = BindingHelper.DeserializeBinding(endpoint.Binding.BindingConfig);
            try
            {
                serviceUri = new Uri(myBinding.Scheme.ToString() + "://" + endpoint.Address);
            }
            catch(Exception ex) 
            { 
                throw new ApplicationException(string.Format("Failed to create service Uri due to error. Endpoint address may be invalid [{0}]",endpoint.Address),ex); 
            }
            Boolean Soap12 = (myBinding.MessageVersion.Envelope.ToString() == "Soap12 (http://www.w3.org/2003/05/soap-envelope)");
            Boolean Soap11 = (myBinding.MessageVersion.Envelope.ToString() == "Soap11 (http://schemas.xmlsoap.org/soap/envelope/)");
            // NZ Code Added
            // This flag is used to take care of any soap specific XML tags
            Boolean isSoap = Soap11 || Soap12;

            //string[] tokens = endpoint.Address.Split(new char[] { '/' }); 
            //int svcIdx = tokens.Length - 1;
            //while ((svcIdx >= 0) && (string.IsNullOrEmpty(tokens[svcIdx]))) svcIdx--;
            //virtualServiceName = (string.IsNullOrEmpty(tokens[svcIdx])) ? "MyService" : tokens[svcIdx];
            if (endpoint.ServiceName != null & endpoint.ServiceName != "")
                virtualServiceName = endpoint.ServiceName;
            else
            {
                double number;
                if (Double.TryParse(Convert.ToString(serviceUri.DnsSafeHost.Substring(0, 1), CultureInfo.InvariantCulture), System.Globalization.NumberStyles.Any, NumberFormatInfo.InvariantInfo, out number))
                    virtualServiceName = "WS" + serviceUri.DnsSafeHost;
                else
                    virtualServiceName = serviceUri.DnsSafeHost;
            }
            //if (char.IsDigit(virtualServiceName[0])) virtualServiceName = "VSvc_" + virtualServiceName;
            //virtualServiceNS = endpoint.Address;
            if (endpoint.NamespaceURI != null & endpoint.NamespaceURI != "")
                virtualServiceNS = endpoint.NamespaceURI;
            else
                virtualServiceNS = MseConstants.MSENamespace + "/" + endpoint.Binding.Name + "/" + virtualServiceName;
            //} else {
            //    virtualServiceName = services[0].Name;
            //    virtualServiceNS = services[0].NamespaceURI;
            //}

            using (XmlWriter writer = XmlWriter.Create(buffer, writerSettings))
            {
                writer.WriteStartElement(SC.WsdlPrefix, "definitions", SC.WsdlNS);
                writer.WriteAttributeString("xmlns", "tns", null, virtualServiceNS);
                writer.WriteAttributeString("targetNamespace", null, virtualServiceNS);
                writer.WriteAttributeString("xmlns", SC.XsdPrefix, null, SC.XsdNS);
                writer.WriteAttributeString("xmlns", SC.SoapEncPrefix, null, SC.SoapEncNS);
                writer.WriteAttributeString("xmlns", SC.WsdlHttpPrefix, null, SC.WsdlHttpNS);
                writer.WriteAttributeString("xmlns", SC.WsdlMimePrefix, null, SC.WsdlMimeNS);
                writer.WriteAttributeString("xmlns", SC.WsdlTMPrefix, null, SC.WsdlTMNS);
                if (Soap11)
                    writer.WriteAttributeString("xmlns", SC.WsdlSoapPrefix, null, SC.WsdlSoapNS);
                if (Soap12)
                    writer.WriteAttributeString("xmlns", SC.WsdlSoap12Prefix, null, SC.WsdlSoap12NS);
                //writer.WriteAttributeString("xmlns", SC.Soap12Prefix, null, SC.Soap12NS);
                //writer.WriteAttributeString("xmlns", SC.SoapPrefix, null, SC.SoapNS);
                prefixes.Add(virtualServiceNS, "tns");
                prefixes.Add(SC.WsdlNS, "wsdl");
                //prefixes.Add(SC.SoapNS, SC.SoapPrefix);
                //prefixes.Add(SC.Soap12NS, SC.Soap12Prefix);                                

                SchemaList schemaList = ListSchemasForEndpoint(endpoint.ID, txn, conn);
                if (schemaList.Schemas != null)
                {
                    foreach (SchemaListElement element in schemaList.Schemas)
                    {
                        Schema schema = GetSchema(element.ID, txn, conn);
                        if (!prefixes.ContainsKey(schema.NamespaceURI))
                        {
                            if (!prefixes.ContainsValue(schema.PreferredPrefix))
                            {
                                prefixes.Add(schema.NamespaceURI, schema.PreferredPrefix);
                            }
                            else
                            { // preferred prefix already taken so use another
                                while (prefixes.ContainsValue(string.Format("s{0}", prefixIdx))) prefixIdx++;
                                prefixes.Add(schema.NamespaceURI, string.Format("s{0}", prefixIdx));
                            }
                            //rawschemas.Append(fixSchemaSelfReferences(schema.XSD.OwnerDocument, prefixes[schema.NamespaceURI], schema.NamespaceURI));
                            // Fixed by Raul
                            XmlDocument schemaDoc = fixSchemaSelfReferences(schema.XSD.OwnerDocument, prefixes[schema.NamespaceURI], schema.NamespaceURI);
                            rawschemas.Append(schemaDoc.OuterXml.Replace("xs:", "xsd:").Replace("xmlns:xs=", "xmlns:xsd="));

                            writer.WriteAttributeString("xmlns", prefixes[schema.NamespaceURI], null, schema.NamespaceURI);
                        }
                    }
                }
                writer.WriteStartElement(SC.WsdlPrefix, "types", null);
                writer.WriteRaw(rawschemas.ToString());
                writer.WriteEndElement();  // end types
                int pt;



                foreach (OperationVersionListElement element in publishedOpVers.OperationVersions)
                {
                    OperationVersion opVersion = GetOperationVersion(element.ID, txn, conn);
                    string opName = opVersion.OperationName;
                    if (opVersion.RequestMsg != null && opVersion.RequestMsg.Count > 0)
                    {
                        writer.WriteStartElement(SC.WsdlPrefix, "message", null);
                        writer.WriteAttributeString("name", string.Format("{0}SoapIn", opName));
                        pt = 0;
                        foreach (MessagePart part in opVersion.RequestMsg)
                        {
                            writer.WriteStartElement(SC.WsdlPrefix, "part", null);
                            // NZ Code Updated
                            // This is done so that WSDL for both MSE hosted and non-hsoted services look similar
                            //writer.WriteAttributeString("name", opVersion.OperationName + "ReqPt" + (pt++));
                            writer.WriteAttributeString("name", "parameters");
                            writer.WriteAttributeString("element", string.Format("{0}:{1}", prefixes[part.NamespaceURI], part.ElementName));
                            writer.WriteEndElement(); // end part
                        }
                        writer.WriteEndElement(); // end message
                    }
                    if (opVersion.ResponseMsg != null && opVersion.ResponseMsg.Count > 0)
                    {
                        writer.WriteStartElement(SC.WsdlPrefix, "message", null);
                        writer.WriteAttributeString("name", string.Format("{0}SoapOut", opName));
                        pt = 0;
                        foreach (MessagePart part in opVersion.ResponseMsg)
                        {
                            writer.WriteStartElement(SC.WsdlPrefix, "part", null);
                            // NZ Code Updated
                            // This is done so that WSDL for both MSE hosted and non-hsoted services look similar
                            //writer.WriteAttributeString("name", opVersion.OperationName + "RespPt" + (pt++));
                            writer.WriteAttributeString("name", "parameters");
                            writer.WriteAttributeString("element", string.Format("{0}:{1}", prefixes[part.NamespaceURI], part.ElementName));
                            writer.WriteEndElement(); // end part
                        }
                        writer.WriteEndElement(); // end message
                    }
                    if (opVersion.SoapFaults != null && opVersion.SoapFaults.Count > 0)
                    {
                        foreach (SoapFault fault in opVersion.SoapFaults)
                        {
                            writer.WriteStartElement(SC.WsdlPrefix, "message", null);
                            writer.WriteAttributeString("name", string.Format("{0}_{1}_FaultMessage", opName, fault.Name));

                            writer.WriteStartElement(SC.WsdlPrefix, "part", null);
                            writer.WriteAttributeString("name", "detail");
                            //writer.WriteAttributeString("name", string.Format("{0}_{1}_FaultPart", opName, fault.Name));
                            writer.WriteAttributeString("element", string.Format("{0}:{1}", prefixes[fault.NamespaceURI], fault.ElementName));
                            writer.WriteEndElement(); // end part

                            writer.WriteEndElement(); // end message
                        }
                    }
                }
                writer.WriteStartElement(SC.WsdlPrefix, "portType", null);
                writer.WriteAttributeString("name", string.Format("{0}Soap", virtualServiceName));
                foreach (OperationVersionListElement element in publishedOpVers.OperationVersions)
                {
                    OperationVersion opVersion = GetOperationVersion(element.ID, txn, conn);
                    // A valid operation should have at least the request message defined
                    if (opVersion.RequestMsg != null && opVersion.RequestMsg.Count > 0)
                    {
                        string opName = opVersion.OperationName;
                        writer.WriteStartElement(SC.WsdlPrefix, "operation", null);
                        writer.WriteAttributeString("name", opName);
                        if (opVersion.RequestMsg != null && opVersion.RequestMsg.Count > 0)
                        {
                            writer.WriteStartElement(SC.WsdlPrefix, "input", null);
                            writer.WriteAttributeString("message", string.Format("tns:{0}SoapIn", opName));
                            writer.WriteEndElement(); // end input
                        }
                        if (opVersion.ResponseMsg != null && opVersion.ResponseMsg.Count > 0)
                        {
                            writer.WriteStartElement(SC.WsdlPrefix, "output", null);
                            writer.WriteAttributeString("message", string.Format("tns:{0}SoapOut", opName));
                            writer.WriteEndElement(); // end output
                        }
                        if (opVersion.SoapFaults != null && opVersion.SoapFaults.Count > 0)
                        {
                            foreach (SoapFault fault in opVersion.SoapFaults)
                            {
                                writer.WriteStartElement(SC.WsdlPrefix, "fault", null);
                                writer.WriteAttributeString("message", string.Format("tns:{0}_{1}_FaultMessage", opName, fault.Name));
                                writer.WriteAttributeString("name", fault.Name);
                                writer.WriteAttributeString("Action", "http://www.w3.org/2006/05/addressing/wsdl", fault.Action);
                                writer.WriteEndElement(); // end output
                            }
                        }
                        writer.WriteEndElement(); // end operation
                    }
                    else
                    {
                        TraceHelper.TraceWarning("Operation Version with Name [{0}] and ID=[{1}] does not have a request message defined. It will not be included in WSDL", opVersion.OperationName, opVersion.ID);
                    }
                }
                // NZ Code Changed
                // Moved this EndElement creation ot of the loop as it was causing
                // XmlFormat exeptions
                writer.WriteEndElement(); // end portType

                if (Soap11)
                {
                    writer.WriteStartElement(SC.WsdlPrefix, "binding", null); // Soap 1.1 bindings
                    writer.WriteAttributeString("name", string.Format("{0}Soap", virtualServiceName));
                    writer.WriteAttributeString("type", string.Format("tns:{0}Soap", virtualServiceName));
                    writer.WriteStartElement(SC.WsdlSoapPrefix, "binding", null);
                    writer.WriteAttributeString("transport", SC.SoapBindingTransport);
                    writer.WriteEndElement(); // End soap11 binding 
                }
                else if (Soap12)
                {
                    writer.WriteStartElement(SC.WsdlPrefix, "binding", null); // Soap 1.2 bindings
                    writer.WriteAttributeString("name", string.Format("{0}Soap12", virtualServiceName));
                    writer.WriteAttributeString("type", string.Format("tns:{0}Soap", virtualServiceName));
                    writer.WriteStartElement(SC.WsdlSoap12Prefix, "binding", null);
                    writer.WriteAttributeString("transport", SC.SoapBindingTransport);
                    writer.WriteEndElement(); // End soap12 binding
                }
                if (isSoap == true)
                {
                    foreach (OperationVersionListElement element in publishedOpVers.OperationVersions)
                    {
                        OperationVersion opVersion = GetOperationVersion(element.ID, txn, conn);
                        string opName = opVersion.OperationName;
                        Operation op = GetOperation(opVersion.OperationID, txn, conn);
                        writer.WriteStartElement(SC.WsdlPrefix, "operation", null);
                        writer.WriteAttributeString("name", opName);
                        //Add documentation of the operation description
                        if (op.Description != null && op.Description != "")
                        {
                            writer.WriteStartElement(SC.WsdlPrefix, "documentation", null);
                            writer.WriteAttributeString("name", "Operation Description");
                            writer.WriteRaw(op.Description);
                            writer.WriteEndElement();
                        }
                        //Add documentation of the version information
                        writer.WriteStartElement(SC.WsdlPrefix, "documentation", null);
                        writer.WriteAttributeString("name", "Version Information");
                        writer.WriteRaw("This operation has been versioned to support revisions.  Your request will automatically be routed to the most current compatible version of this operation without explicitly specifying a version via the SOAPAction.  To explicitly invoke this version of this operation, specify the soap action as " + fullSoapAction(opVersion));
                        writer.WriteEndElement();

                        if (Soap11)
                            writer.WriteStartElement(SC.WsdlSoapPrefix, "operation", null);
                        else if (Soap12)
                            writer.WriteStartElement(SC.WsdlSoap12Prefix, "operation", null);

                        writer.WriteAttributeString("soapAction", opName);
                        writer.WriteAttributeString("style", "document");
                        writer.WriteEndElement(); // end soap:operation element

                        if (opVersion.RequestMsg != null && opVersion.RequestMsg.Count > 0)
                        {
                            writer.WriteStartElement(SC.WsdlPrefix, "input", null);
                            if (Soap11)
                                writer.WriteStartElement(SC.WsdlSoapPrefix, "body", null);
                            else if (Soap12)
                                writer.WriteStartElement(SC.WsdlSoap12Prefix, "body", null);
                            writer.WriteAttributeString("use", "literal");
                            writer.WriteEndElement(); // end soap:body element
                            writer.WriteEndElement(); // end input
                        }
                        if (opVersion.ResponseMsg != null && opVersion.ResponseMsg.Count > 0)
                        {
                            writer.WriteStartElement(SC.WsdlPrefix, "output", null);
                            if (Soap11)
                                writer.WriteStartElement(SC.WsdlSoapPrefix, "body", null);
                            else if (Soap12)
                                writer.WriteStartElement(SC.WsdlSoap12Prefix, "body", null);
                            writer.WriteAttributeString("use", "literal");
                            writer.WriteEndElement(); // end soap:body element
                            writer.WriteEndElement(); // end input
                        }
                        if (opVersion.SoapFaults != null && opVersion.SoapFaults.Count > 0)
                        {
                            foreach (SoapFault fault in opVersion.SoapFaults)
                            {
                                writer.WriteStartElement(SC.WsdlPrefix, "fault", null);
                                writer.WriteAttributeString("name", fault.Name);

                                if (Soap11)
                                {
                                    writer.WriteStartElement(SC.WsdlSoapPrefix, "fault", null);
                                }
                                else if (Soap12)
                                {
                                    writer.WriteStartElement(SC.WsdlSoap12Prefix, "fault", null);
                                }
                                writer.WriteAttributeString("name", fault.Name);
                                writer.WriteAttributeString("use", "literal");
                                writer.WriteEndElement(); // end WSDL:fault
                                writer.WriteEndElement(); // end SOAP:fault
                            }
                        }
                        writer.WriteEndElement(); // end operation

                    }
                }
                // NZ Code Changed
                // Moved this EndElement creation ot of the loop as it was causing
                // XmlFormat exeptions
                writer.WriteEndElement(); // end binding

                if (isSoap == true)
                {
                    writer.WriteStartElement(SC.WsdlPrefix, "service", null);
                    writer.WriteAttributeString("name", string.Format("{0}", virtualServiceName));

                    //SOAP1.1 Service Definition
                    if (Soap11)
                    {
                        writer.WriteStartElement(SC.WsdlPrefix, "port", null); // Soap 11 Port
                        writer.WriteAttributeString("name", string.Format("{0}Soap", virtualServiceName));
                        writer.WriteAttributeString("binding", string.Format("tns:{0}Soap", virtualServiceName));
                        writer.WriteStartElement(SC.WsdlSoapPrefix, "address", null);
                        writer.WriteAttributeString("location", serviceUri.AbsoluteUri);
                        writer.WriteEndElement(); // end soap:address element
                        writer.WriteEndElement(); // end port element
                    }
                    //SOAP1.2 Service Definition
                    if (Soap12)
                    {
                        writer.WriteStartElement(SC.WsdlPrefix, "port", null); // Soap 12 Port
                        writer.WriteAttributeString("name", string.Format("{0}Soap12", virtualServiceName));
                        writer.WriteAttributeString("binding", string.Format("tns:{0}Soap12", virtualServiceName));
                        writer.WriteStartElement(SC.WsdlSoap12Prefix, "address", null);
                        writer.WriteAttributeString("location", serviceUri.AbsoluteUri);
                        writer.WriteEndElement(); // end soap:address element
                        writer.WriteEndElement(); // end port element
                        //
                    }
                    writer.WriteEndElement(); // end service
                    writer.WriteEndElement(); // end definitions
                }
                writer.Flush();
            }

            response.LoadXml(buffer.ToString());

            return response.DocumentElement;
        }

        public static OperationGroup GetOperationGroup(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            OperationGroup operationGroup = new OperationGroup();
            const string sprocname = "[ServiceEngine].[GetOperationGroup]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            operationGroup.ID = reader.GetGuid(0);
                            operationGroup.Name = reader.GetString(1);
                            if (reader.IsDBNull(2))
                            {
                                operationGroup.Description = null;
                            }
                            else
                            {
                                operationGroup.Description = reader.GetString(2);
                            }
                        }
                    }
                }
                operationGroup.Operations = ListOperationsByGroup(ID, txn, conn);
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return operationGroup;

        }

        public static Operation GetOperation(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            Operation operation = new Operation();
            const string sprocname = "[ServiceEngine].[GetOperation]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            operation.ID = reader.GetGuid(0);
                            operation.Name = reader.GetString(1);
                            if (reader.IsDBNull(2))
                            {
                                operation.Description = null;
                            }
                            else
                            {
                                operation.Description = reader.GetString(2);
                            }
                            if (reader.IsDBNull(3))
                            {
                                operation.Policy = null;
                            }
                            else
                            {
                                operation.Policy = GetPolicy(reader.GetGuid(3), txn, conn);
                            }
                        }
                    }
                }
                OperationVersionList list = ListOperationVersions(ID, txn, conn);

                if (list.OperationVersions != null)
                {
                    operation.Versions = new List<OperationVersion>();
                    foreach (OperationVersionListElement version in list.OperationVersions)
                    {
                        operation.Versions.Add(GetOperationVersion(version.ID, txn, conn));
                    }
                }
                operation.EndpointList = ListAssociatedEndpointsForOperation(ID, txn, conn);

            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return operation;

        }

        private static EndpointList ListAssociatedEndpointsForOperation(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            EndpointList list = new EndpointList();
            const string sprocname = "[ServiceEngine].[ListAssociatedEndpointsForOperationID]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@OperationID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Endpoints = new List<EndpointListElement>();
                            while (reader.Read())
                            {
                                EndpointListElement element = new EndpointListElement();
                                element.ID = reader.GetGuid(0);
                                element.URI = reader.GetString(1);
                                list.Endpoints.Add(element);
                            }
                        }
                    }
                }
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;
        }

        private static OperationVersionList ListOperationVersions(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            OperationVersionList list = new OperationVersionList();
            const string sprocname = "[ServiceEngine].[ListOperationVersions]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@OperationID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.OperationVersions = new List<OperationVersionListElement>();
                            while (reader.Read())
                            {
                                OperationVersionListElement element = new OperationVersionListElement();
                                element.Version = new VersionNumber();
                                element.ID = reader.GetGuid(0);
                                VersionNumber verNumber = new VersionNumber();
                                verNumber.Major = reader.GetInt32(1).ToString();
                                verNumber.Minor = reader.GetInt32(2).ToString();
                                verNumber.Build = reader.GetInt32(3).ToString();
                                verNumber.Qfe = reader.GetInt32(4).ToString();
                                element.Version = verNumber;
                                list.OperationVersions.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;

        }

        public static OperationVersion GetOperationVersion(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            OperationVersion version = new OperationVersion();
            const string sprocname = "[ServiceEngine].[GetOperationVersion]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            version.ID = reader.GetGuid(0);
                            version.OperationID = reader.GetGuid(1);
                            version.OperationName = reader.GetString(2);
                            VersionNumber verNumber = new VersionNumber();
                            verNumber.Major = reader.GetInt32(3).ToString();
                            verNumber.Minor = reader.GetInt32(4).ToString();
                            verNumber.Build = reader.GetInt32(5).ToString();
                            verNumber.Qfe = reader.GetInt32(6).ToString();
                            version.Version = verNumber;
                            if (reader.IsDBNull(7))
                            {
                                version.AutoResponse = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(7).CreateReader());
                                version.AutoResponse = xdoc.DocumentElement;

                            }
                            version.IsPublic = reader.GetBoolean(8);
                            version.IsActive = reader.GetBoolean(9);
                            version.IsTestable = reader.GetBoolean(10);
                            version.RequestMsg = GetMessage(version.ID, SC.IsRequestMsg, txn, conn);
                            version.ResponseMsg = GetMessage(version.ID, SC.IsResponseMsg, txn, conn);

                            if (reader.IsDBNull(11))
                            {
                                version.RequestTransformXSLT = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(11).CreateReader());
                                version.RequestTransformXSLT = xdoc.DocumentElement;
                            }

                            if (reader.IsDBNull(12))
                            {
                                version.ResponseTransformXSLT = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(12).CreateReader());
                                version.ResponseTransformXSLT = xdoc.DocumentElement;
                            }

                            if (reader.IsDBNull(13))
                            {
                                version.Channel = null;
                            }
                            else
                            {
                                Channel chnl = GetChannel(reader.GetGuid(13), txn, conn);
                                version.Channel = chnl;
                            }

                            if (reader.IsDBNull(14))
                            {
                                version.Moniker = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(14).CreateReader());
                                version.Moniker = xdoc.DocumentElement;
                            }
                            if (reader.IsDBNull(15))
                            {
                                version.Description = null;
                            }
                            else
                            {
                                version.Description = reader.GetString(15);
                            }
                            // Get SoapFaults
                            version.SoapFaults = GetSoapFaults(ID, txn, conn);
                        }
                    }
                }
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return version;

        }

        public static Channel GetChannel(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            Channel chnl = new Channel();
            const string sprocname = "[ServiceEngine].[GetChannel]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            chnl.ID = reader.GetGuid(0);
                            chnl.Name = reader.GetString(1);
                            XmlDocument xdoc = new XmlDocument();
                            xdoc.Load(reader.GetSqlXml(2).CreateReader());
                            chnl.MonikerSchema = xdoc.DocumentElement;
                            chnl.Binding = GetMSEBinding(reader.GetGuid(3), txn, conn);
                            xdoc = new XmlDocument();

                            chnl.MonikerEditorAssemblyName = reader.GetString(4);
                            chnl.MonikerEditorTypeName = reader.GetString(5);
                            if (reader.IsDBNull(6))
                            {
                                chnl.Description = null;
                            }
                            else
                            {
                                chnl.Description = reader.GetString(6);
                            }
                        }

                        //Get policies SGK 05/06/2008
                        chnl.Policies = GetEntityPolicies(chnl.ID, txn, conn);
                    }
                }


            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return chnl;
        }

        public static List<MessagePart> GetMessage(Guid ID, bool isRequestMsg, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<MessagePart> message = new List<MessagePart>();
            const string sprocname = "[ServiceEngine].[GetMessage]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@OperationVersionID", SqlDbType.UniqueIdentifier);
                    SqlParameter IsRequestMsgParam = new SqlParameter("@IsRequestMsg", SqlDbType.Bit);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    IsRequestMsgParam.Value = isRequestMsg;
                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(IsRequestMsgParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                MessagePart part = new MessagePart();
                                part.ElementName = reader.GetString(2);
                                part.NamespaceURI = reader.GetString(3);
                                message.Add(part);
                            }
                        }
                    }
                }


            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return message;

        }

        public static List<SoapFault> GetSoapFaults(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<SoapFault> soapFaults = new List<SoapFault>();
            const string sprocname = "[ServiceEngine].[GetSoapFaultsForOperationVersion]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@OperationVersionID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                SoapFault fault = new SoapFault();
                                fault.NamespaceURI = reader.GetString(0);
                                fault.ID = reader.GetGuid(1);
                                fault.Name = reader.GetString(2);
                                fault.Action = reader.GetString(3);
                                fault.ProtectionLevel = (System.Net.Security.ProtectionLevel)Enum.Parse(typeof(System.Net.Security.ProtectionLevel), reader.GetString(4));
                                fault.ElementName = reader.GetString(5);
                                soapFaults.Add(fault);
                            }
                        }
                    }
                }


            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return soapFaults;

        }


        public static XmlDocument GetSchemaByNamespace(string namespaceUri, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;

            const string sprocname = "[ServiceEngine].[GetSchemaByNamespace]";
            XmlDocument schemaDOM = new XmlDocument();
            XmlSchema schema = null;

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter NamespaceParam = new SqlParameter("@Namespace", SqlDbType.NVarChar);

                    cmd.CommandType = CommandType.StoredProcedure;

                    NamespaceParam.Value = namespaceUri;
                    cmd.Parameters.Add(NamespaceParam);
                    //get the schema for the MessagePart
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            schemaDOM.Load(reader.GetSqlXml(3).CreateReader());
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                if (autoclose) { conn.Close(); }
                throw ex;
            }

            return schemaDOM;
        }

        private static void LoadImportedSchemas(XmlSchemaSet schemaSet, Dictionary<string, string> schemaHash, XmlDocument schemaDoc,
                                                SqlTransaction txn, SqlConnection conn)
        {
            XmlNamespaceManager xmlns = new XmlNamespaceManager(schemaDoc.NameTable);
            xmlns.AddNamespace("xsd", XmlSchema.Namespace);
            XmlNodeList importNodes = schemaDoc.SelectNodes("//xsd:import", xmlns);
            foreach (XmlNode xn in importNodes)
            {
                string name = null;
                if (xn.Attributes["namespace"] != null) name = xn.Attributes["namespace"].Value;

                if (schemaHash.ContainsKey(name) == false)
                {
                    XmlDocument importedSchemaDoc = GetSchemaByNamespace(name, txn, conn);
                    XmlSchema schema = XmlSchema.Read(new StringReader(importedSchemaDoc.OuterXml), null);
                    schemaSet.Add(schema);
                    schemaHash.Add(name, name);

                    LoadImportedSchemas(schemaSet, schemaHash, importedSchemaDoc, txn, conn);
                }
            }

        }

        public static Boolean ValidateOperationVersionRequest(XmlElement request, Guid opVersionID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            XmlDocument tempXMLDoc = new XmlDocument();
            XmlSchemaSet schemaSet = new XmlSchemaSet();
            XmlReaderSettings settings = new XmlReaderSettings();

            XmlDocument mpSchema = new XmlDocument();
            String xsdNS;
            Dictionary<string, string> schemaHash = new Dictionary<string, string>();


            try
            {
                tempXMLDoc.LoadXml(request.OuterXml);

                OperationVersion opVer = GetOperationVersion(opVersionID, txn, conn);

                // Before we do any validation, we may want to check if 
                // the request's document element name is the same as specified in request messsage part
                // Will have to modify for multipart message
                if (opVer.RequestMsg != null && opVer.RequestMsg.Count > 0)
                {
                    if (string.Compare(request.LocalName, opVer.RequestMsg[0].ElementName, false) != 0)
                    {
                        Console.WriteLine("Request's DocumentElement name [{0}] is different from Request's Message Part Element Name [{1}]", request.LocalName, opVer.RequestMsg[0].ElementName);
                        return false;
                    }
                }
                else
                {
                    Console.WriteLine("Request's Message Part for Version [{0}] does not exist", opVer.Version.ToString());
                    return false;
                }

                foreach (MessagePart reqMP in opVer.RequestMsg)
                {
                    XmlDocument xd = GetSchemaByNamespace(reqMP.NamespaceURI, txn, conn);
                    if (xd != null)
                    {
                        if (schemaHash.ContainsKey(reqMP.NamespaceURI) == false)
                        {
                            XmlSchema schema = XmlSchema.Read(new StringReader(xd.OuterXml), null);
                            schemaSet.Add(schema);
                            schemaHash.Add(reqMP.NamespaceURI, reqMP.NamespaceURI);
                            LoadImportedSchemas(schemaSet, schemaHash, xd, txn, conn);
                        }
                    }
                }

                try
                {
                    schemaSet.Compile();
                }
                catch (Exception ex)
                {
                    //return false;
                    throw new ApplicationException("Failed to validate Request Message Schema due to error [" + ex.Message + "]");
                }

                try
                {
                    settings.Schemas = schemaSet;
                    settings.ConformanceLevel = ConformanceLevel.Auto;
                    settings.ValidationType = ValidationType.Schema;
                    settings.ValidationFlags |= XmlSchemaValidationFlags.ProcessInlineSchema;
                    settings.ValidationFlags |= XmlSchemaValidationFlags.ReportValidationWarnings;

                    bool hasError = false;
                    // Warning validation only works if we have an event handler
                    settings.ValidationEventHandler += delegate(object sender, ValidationEventArgs e)
                    {
                        Console.WriteLine("Validation Error:[{0}]", GetExceptionDetails(e.Exception));
                        hasError = true;
                    };

                    // Create the XmlReader object.
                    XmlReader reader = XmlReader.Create(new StringReader(tempXMLDoc.OuterXml), settings);

                    // Parse the document. An XMLException should be thrown if there is an validation error
                    while (reader.Read())
                    {
                        if (hasError == true)
                            break;
                        Console.WriteLine(reader.Name + reader.NamespaceURI);
                    }
                    // This will only return true if there is no error
                    return (hasError == false);
                }

                catch (Exception ex)
                {
                    //return false;
                    throw new ApplicationException("Failed to validate Request Message due to error [" + ex.Message + "]");
                }

            }
            catch (ApplicationException aex)
            {
                if (autoclose) { conn.Close(); }
                throw aex;
            }
            catch (Exception ex)
            {
                if (autoclose) { conn.Close(); }
                return false;
            }
        }


        public static Schema GetSchema(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            Schema schema = new Schema();
            const string sprocname = "[ServiceEngine].[GetSchema]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            schema.ID = reader.GetGuid(0);
                            schema.NamespaceURI = reader.GetString(1);
                            schema.PreferredPrefix = reader.GetString(2);
                            XmlDocument xdoc = new XmlDocument();
                            xdoc.Load(reader.GetSqlXml(3).CreateReader());
                            schema.XSD = xdoc.DocumentElement;
                            if (reader.IsDBNull(4))
                            {
                                schema.Description = null;
                            }
                            else
                            {
                                schema.Description = reader.GetString(4);
                            }
                        }
                    }
                }


            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return schema;

        }

        public static void DeleteSchema(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteSchema]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        public static SchemaList ListSchemasForEndpoint(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            SchemaList list = new SchemaList();
            const string sprocname = "[ServiceEngine].[ListSchemasForEndpoint]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Schemas = new List<SchemaListElement>();
                            HashSet<string> namespaces = new HashSet<string>();
                            while (reader.Read())
                            {
                                SchemaListElement element = new SchemaListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                string schema = reader.GetString(2);
                                if (reader.IsDBNull(3))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(3);
                                }
                                list.Schemas.Add(element);
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.LoadXml(schema);
                                XmlNamespaceManager xmlns = new XmlNamespaceManager(xdoc.NameTable);
                                xmlns.AddNamespace("xs", XmlSchema.Namespace);
                                foreach (XmlElement xel in xdoc.SelectNodes("//xs:import", xmlns))
                                {
                                    ResolveImports(xel,namespaces, list, txn, conn);
                                }

                            }

                        }
                    }
                }

            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;
        }

        private static void ResolveImports(XmlElement xel, HashSet<string> namespaces, SchemaList list, SqlTransaction txn, SqlConnection conn)
        {
            string impns = xel.Attributes["namespace"].Value;
            Guid impschemaid = GetIDFromName(impns, EntityClass.Schema, txn, conn);
            if (!impschemaid.Equals(Guid.Empty) && namespaces.Contains(impns) == false)
            {
                namespaces.Add(impns);
                SchemaListElement element = new SchemaListElement();
                element.ID = impschemaid;
                element.Name = impns;
                list.Schemas.Add(element);
                Schema s = GetSchema(element.ID, txn, conn);
                XmlDocument xdoc = new XmlDocument();
                xdoc.LoadXml(s.XSD.OuterXml);
                XmlNamespaceManager xmlns = new XmlNamespaceManager(xdoc.NameTable);
                xmlns.AddNamespace("xs", XmlSchema.Namespace);
                foreach (XmlElement xel2 in xdoc.SelectNodes("//xs:import", xmlns))
                {
                    ResolveImports(xel2,namespaces, list, txn, conn);
                }

            }
        }

        public static SchemaList ListSchemas(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            SchemaList list = new SchemaList();
            const string sprocname = "[ServiceEngine].[ListSchemas]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Schemas = new List<SchemaListElement>();
                            while (reader.Read())
                            {
                                SchemaListElement element = new SchemaListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.Schemas.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;
        }

        private static XmlDocument fixSchemaSelfReferences(XmlDocument schema, string prefix, string ns)
        {
            //TODO: Reengineer this for more robust solution

            string oldNS = string.Empty;
            XmlNamespaceManager nsmgr = new XmlNamespaceManager(schema.NameTable);
            nsmgr.AddNamespace(SC.XsdPrefix, SC.XsdNS);
            XmlNode nsAttrInSchema = schema.SelectSingleNode("/xsd:schema/@targetNamespace", nsmgr);
            List<string> oldPrefixes = new List<string>();
            List<XmlAttribute> oldAttrs = new List<XmlAttribute>();

            if (nsAttrInSchema != null) oldNS = nsAttrInSchema.Value;
            if (!oldNS.Equals(ns))
            { // Schema has been "renamed" so let's fix this
                if (nsAttrInSchema == null)
                {
                    nsAttrInSchema = schema.CreateAttribute("targetNamespace");
                    schema.SelectSingleNode("/xsd:schema", nsmgr).Attributes.Append((XmlAttribute)nsAttrInSchema);
                }
                nsAttrInSchema.Value = ns;
                string newschema = schema.OuterXml;
                newschema.Replace(oldNS, ns);
                schema = new XmlDocument();
                schema.LoadXml(newschema);
                nsmgr = new XmlNamespaceManager(schema.NameTable);
                nsmgr.AddNamespace(SC.XsdPrefix, SC.XsdNS);
            }
            //            XmlDocument schemaElement = (XmlDocument)schema.SelectSingleNode("/xsd:schema", nsmgr);
            XmlDocument schemaElement = new XmlDocument();
            schemaElement.LoadXml(schema.SelectSingleNode("/xsd:schema", nsmgr).OuterXml);
            XmlAttribute xmlnsAttr = null;
            if (schemaElement.Attributes != null)
            {
                foreach (XmlAttribute attr in schemaElement.Attributes)
                {
                    if (attr.LocalName.Equals("xmlns"))
                    {
                        xmlnsAttr = attr;
                    }
                }
            }

            if (xmlnsAttr != null)
            { // Visual Studio has a fit if the Schema uses xmlns= to identify the types so fix this
                schemaElement.Attributes.Remove(xmlnsAttr);
                foreach (XmlNode attr in schema.SelectNodes("//@type"))
                {
                    if (!attr.Value.Contains(":"))
                    {
                        attr.Value = string.Format("{0}:{1}", prefix, attr.Value);
                    }
                }
            }

            return schema;
        }


        private static string fullSoapAction(OperationVersion version)
        {
            string response = string.Empty;

            if (version == null) throw new ArgumentNullException("version");

            if ((!string.IsNullOrEmpty(version.Version.Qfe)) && (int.Parse(version.Version.Qfe, nfi) != 0))
            {
                response = string.Format(nfi, "{0}:{1}.{2}.{3}.{4}", version.OperationName, version.Version.Major, version.Version.Minor, version.Version.Build, version.Version.Qfe);
            }
            else
            {
                if ((!string.IsNullOrEmpty(version.Version.Build)) && (int.Parse(version.Version.Build, nfi) != 0))
                {
                    response = string.Format(nfi, "{0}:{1}.{2}.{3}", version.OperationName, version.Version.Major, version.Version.Minor, version.Version.Build);
                }
                else
                {
                    response = string.Format(nfi, "{0}:{1}.{2}", version.OperationName, version.Version.Major, version.Version.Minor);
                }
            }

            return response;
        }


        public static Schema UpdateSchema(Schema schema, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateSchema]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter PreferredPrefixParam = new SqlParameter("@PreferredPrefix", SqlDbType.NVarChar, 10);
                    SqlParameter XSDParam = new SqlParameter("@Schema", SqlDbType.Xml);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = schema.ID;
                    if (schema.NamespaceURI != null)
                    {
                        NameParam.Value = schema.NamespaceURI;
                    }
                    else
                    {
                        throw new ApplicationException("UpdateSchema failed: NamespaceURI is a required element but was not sent");
                    }
                    if (schema.XSD != null)
                    {
                        XSDParam.Value = schema.XSD.OuterXml;
                    }
                    else
                    {
                        throw new ApplicationException("UpdateSchema failed: XSD is a required element but was not sent");
                    }
                    PreferredPrefixParam.Value = schema.PreferredPrefix;
                    DescriptionParam.Value = schema.Description;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(PreferredPrefixParam);
                    cmd.Parameters.Add(XSDParam);
                    cmd.Parameters.Add(DescriptionParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return schema;
        }

        public static List<OperationListElement> ListOperationsByGroup(Guid operationGroupID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<OperationListElement> list = new List<OperationListElement>();
            const string sprocname = "[ServiceEngine].[ListOperationsByGroup]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@operationGroupID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = operationGroupID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list = new List<OperationListElement>();
                            while (reader.Read())
                            {
                                OperationListElement element = new OperationListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;

        }

        public static OperationList ListOperationsByElement(Guid SchemaID, string elementName, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            OperationList list = new OperationList();
            const string sprocname = "[ServiceEngine].[ListOperationsByElement]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@SchemaID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@ElementName", SqlDbType.NVarChar, 400);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = SchemaID;
                    cmd.Parameters.Add(IDParam);
                    NameParam.Value = elementName;
                    cmd.Parameters.Add(NameParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Operations = new List<OperationListElement>();
                            while (reader.Read())
                            {
                                OperationListElement element = new OperationListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Operations.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;

        }


        public static OperationList ListOperationsBySchema(Guid SchemaID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            OperationList list = new OperationList();
            const string sprocname = "[ServiceEngine].[ListOperationsBySchema]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@SchemaID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = SchemaID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Operations = new List<OperationListElement>();
                            while (reader.Read())
                            {
                                OperationListElement element = new OperationListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Operations.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;

        }


        public static OperationList ListOperationsByFirstElement(Guid SchemaID, string elementName, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            OperationList list = new OperationList();
            const string sprocname = "[ServiceEngine].[ListOperationsByFirstElement]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@SchemaID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@ElementName", SqlDbType.NVarChar, 400);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = SchemaID;
                    cmd.Parameters.Add(IDParam);
                    NameParam.Value = elementName;
                    cmd.Parameters.Add(NameParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Operations = new List<OperationListElement>();
                            while (reader.Read())
                            {
                                OperationListElement element = new OperationListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.Operations.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;

        }


        public static Guid CreateSchema(Schema schema, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateSchema]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter PreferredPrefixParam = new SqlParameter("@PreferredPrefix", SqlDbType.NVarChar, 10);
                    SqlParameter XSDParam = new SqlParameter("@Schema", SqlDbType.Xml);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    NameParam.Value = schema.NamespaceURI;
                    if (schema.XSD != null)
                    {
                        XSDParam.Value = schema.XSD.OuterXml;
                    }
                    else
                    {
                        throw new ApplicationException("CreateSchema failed: XSD is a required element but was not sent");
                    }
                    PreferredPrefixParam.Value = schema.PreferredPrefix;
                    DescriptionParam.Value = schema.Description;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(PreferredPrefixParam);
                    cmd.Parameters.Add(XSDParam);
                    cmd.Parameters.Add(DescriptionParam);

                    cmd.ExecuteNonQuery();
                    ID = (Guid)IDParam.Value;
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return ID;
        }

        public static bool EntityExists(Guid entityID, EntityClass entityClass, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            bool exists;
            const string sprocname = "[ServiceEngine].[EntityExists]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter entityIDParam = new SqlParameter("@EntityID", SqlDbType.UniqueIdentifier);
                    SqlParameter entityClassParam = new SqlParameter("@EntityClass", SqlDbType.Int);
                    SqlParameter entityExistsParam = new SqlParameter("@EntityExists", SqlDbType.Bit);

                    cmd.CommandType = CommandType.StoredProcedure;
                    entityIDParam.Value = entityID;
                    entityExistsParam.Direction = ParameterDirection.Output;
                    entityClassParam.Value = (int)entityClass;
                    cmd.Parameters.Add(entityExistsParam);
                    cmd.Parameters.Add(entityClassParam);
                    cmd.Parameters.Add(entityIDParam);

                    cmd.ExecuteNonQuery();

                    exists = (bool)entityExistsParam.Value;
                }
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return exists;
        }

        public static OperationGroup CreateOperationGroup(OperationGroup operationGroup, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateOperationGroup]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }


                if (txn == null) txn = conn.BeginTransaction(IsolationLevel.Snapshot);

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    NameParam.Value = operationGroup.Name;
                    DescriptionParam.Value = operationGroup.Description;


                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);

                    cmd.ExecuteNonQuery();

                    ID = (Guid)IDParam.Value;
                    operationGroup.ID = ID;

                }

                if (operationGroup.Operations != null)
                {
                    foreach (OperationListElement operation in operationGroup.Operations)
                    {
                        AssignOperationToGroup(operationGroup.ID, operation.ID, txn, conn);
                    }
                }
                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return operationGroup;
        }
        public static void AssignOperationToGroup(Guid operationGroupID, Guid operationID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[AssignOperationToOperationGroup]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter OperationGroupIDParam = new SqlParameter("@OperationGroupID", SqlDbType.UniqueIdentifier);
                    SqlParameter OperationIDParam = new SqlParameter("@OperationID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    OperationGroupIDParam.Value = operationGroupID;
                    cmd.Parameters.Add(OperationGroupIDParam);
                    OperationIDParam.Value = operationID;
                    cmd.Parameters.Add(OperationIDParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        public static void RemoveOperationFromGroup(Guid operationGroupID, Guid operationID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemoveOperationFromOperationGroup]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter OperationGroupIDParam = new SqlParameter("@OperationGroupID", SqlDbType.UniqueIdentifier);
                    SqlParameter OperationIDParam = new SqlParameter("@OperationID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    OperationGroupIDParam.Value = operationGroupID;
                    cmd.Parameters.Add(OperationGroupIDParam);
                    OperationIDParam.Value = operationID;
                    cmd.Parameters.Add(OperationIDParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        public static Guid CreateOperation(Operation operation, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            Guid policyID = Guid.Empty;
            Guid implementationID = Guid.Empty;
            int sequenceNumber = 0;
            const string sprocname = "[ServiceEngine].[CreateOperation]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                if (txn == null) txn = conn.BeginTransaction(IsolationLevel.Snapshot);

                if (operation.Policy != null)
                {
                    if ((operation.Policy.ID == null) || (!EntityExists(operation.Policy.ID, EntityClass.Policy, txn, conn)))
                    {
                        policyID = CreatePolicy(operation.Policy, txn, conn);
                    }
                    else
                    {
                        policyID = operation.Policy.ID;
                    }
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    NameParam.Value = operation.Name;
                    DescriptionParam.Value = operation.Description;

                    if (policyID.Equals(Guid.Empty))
                    {
                        PolicyIDParam.Value = DBNull.Value;
                    }
                    else
                    {
                        PolicyIDParam.Value = policyID;
                    }

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(PolicyIDParam);

                    cmd.ExecuteNonQuery();

                    ID = (Guid)IDParam.Value;

                    operation.ID = ID;
                }

                if (operation.Versions != null)
                {
                    foreach (OperationVersion version in operation.Versions)
                    {
                        version.OperationID = operation.ID;
                        CreateOperationVersion(version, txn, conn);
                    }
                }
                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return ID;
        }

        public static Guid CreateOperationVersion(OperationVersion version, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            Guid implementationID = Guid.Empty;
            const string sprocname = "[ServiceEngine].[CreateOperationVersion]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                if (txn == null) txn = conn.BeginTransaction(IsolationLevel.Snapshot);

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter OperationIDParam = new SqlParameter("@OperationID", SqlDbType.UniqueIdentifier);
                    SqlParameter VersionMajorParam = new SqlParameter("@VersionMajor", SqlDbType.Int);
                    SqlParameter VersionMinorParam = new SqlParameter("@VersionMinor", SqlDbType.Int);
                    SqlParameter VersionBuildParam = new SqlParameter("@VersionBuild", SqlDbType.Int);
                    SqlParameter VersionQFEParam = new SqlParameter("@VersionQFE", SqlDbType.Int);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter AutoResponseParam = new SqlParameter("@AutoResponse", SqlDbType.Xml);
                    SqlParameter IsPublicParam = new SqlParameter("@IsPublic", SqlDbType.Bit);
                    SqlParameter IsActiveParam = new SqlParameter("@IsActive", SqlDbType.Bit);
                    SqlParameter IsTestableParam = new SqlParameter("@IsTestable", SqlDbType.Bit);
                    SqlParameter RequestTransformParam = new SqlParameter("@RequestTransform", SqlDbType.Xml);
                    SqlParameter ResponseTransformParam = new SqlParameter("@ResponseTransform", SqlDbType.Xml);
                    SqlParameter ChannelIDParam = new SqlParameter("@ChannelID", SqlDbType.UniqueIdentifier);
                    SqlParameter MonikerParam = new SqlParameter("@Moniker", SqlDbType.Xml);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    OperationIDParam.Value = version.OperationID;
                    VersionMajorParam.Value = int.Parse(version.Version.Major);
                    VersionMinorParam.Value = int.Parse(version.Version.Minor);
                    VersionBuildParam.Value = int.Parse(version.Version.Build);
                    VersionQFEParam.Value = int.Parse(version.Version.Qfe);
                    DescriptionParam.Value = version.Description;
                    if (version.AutoResponse != null)
                    {
                        AutoResponseParam.Value = version.AutoResponse.OuterXml;
                    }
                    else
                    {
                        AutoResponseParam.Value = DBNull.Value;
                    }
                    IsPublicParam.Value = version.IsPublic;
                    IsActiveParam.Value = version.IsActive;
                    IsTestableParam.Value = version.IsTestable;
                    if (version.RequestTransformXSLT != null)
                    {
                        RequestTransformParam.Value = version.RequestTransformXSLT.OuterXml;
                    }
                    else
                    {
                        RequestTransformParam.Value = DBNull.Value;
                    }
                    if (version.ResponseTransformXSLT != null)
                    {
                        ResponseTransformParam.Value = version.ResponseTransformXSLT.OuterXml;
                    }
                    else
                    {
                        ResponseTransformParam.Value = DBNull.Value;
                    }
                    if (version.Channel != null)
                    {
                        if (version.Channel.ID.Equals(Guid.Empty))
                        {
                            ChannelIDParam.Value = CreateChannel(version.Channel, txn, conn);
                        }
                        else
                        {
                            ChannelIDParam.Value = version.Channel.ID;
                        }
                    }
                    else
                    {
                        ChannelIDParam.Value = null;
                        //throw new ApplicationException("CreateOperationVersion requires ChannelType, but no ChannelType was supplied");
                    }
                    if (version.Moniker != null)
                    {
                        MonikerParam.Value = version.Moniker.OuterXml;
                    }
                    else
                    {
                        MonikerParam.Value = null;
                        //throw new ApplicationException("CreateOperationVersion failed: Moniker is a required element but was not sent");
                    }

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(OperationIDParam);
                    cmd.Parameters.Add(VersionMajorParam);
                    cmd.Parameters.Add(VersionMinorParam);
                    cmd.Parameters.Add(VersionBuildParam);
                    cmd.Parameters.Add(VersionQFEParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(AutoResponseParam);
                    cmd.Parameters.Add(IsPublicParam);
                    cmd.Parameters.Add(IsActiveParam);
                    cmd.Parameters.Add(IsTestableParam);
                    cmd.Parameters.Add(RequestTransformParam);
                    cmd.Parameters.Add(ResponseTransformParam);
                    cmd.Parameters.Add(ChannelIDParam);
                    cmd.Parameters.Add(MonikerParam);

                    cmd.ExecuteNonQuery();
                    ID = (Guid)IDParam.Value;
                }

                int partNo = 0;

                if (version.RequestMsg != null)
                {
                    foreach (MessagePart part in version.RequestMsg)
                    {
                        CreateMessagePart(part, ID, partNo++, SC.IsRequestMsg, txn, conn);
                    }
                }

                if (version.ResponseMsg != null)
                {
                    partNo = 0;
                    foreach (MessagePart part in version.ResponseMsg)
                    {
                        CreateMessagePart(part, ID, partNo++, SC.IsResponseMsg, txn, conn);
                    }
                }
                // Create Soap Faults
                if (version.SoapFaults != null && version.SoapFaults.Count > 0)
                {
                    partNo = 0;
                    foreach (SoapFault fault in version.SoapFaults)
                    {
                        CreateSoapFault(fault, ID, txn, conn);
                    }
                }
                
                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return ID;
        }

        private static void CreateMessagePart(MessagePart part, Guid operationVersionID, int ordinal, bool isRequestMsg, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateMessagePart]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter OperationVersionIDParam = new SqlParameter("@OperationVersionID", SqlDbType.UniqueIdentifier);
                    SqlParameter IsRequestMsgParam = new SqlParameter("@IsRequestMsg", SqlDbType.Bit);
                    SqlParameter OrdinalParam = new SqlParameter("@Ordinal", SqlDbType.Int);
                    SqlParameter ElementNameParam = new SqlParameter("@ElementName", SqlDbType.NVarChar, 400);
                    SqlParameter SchemaIDParam = new SqlParameter("@SchemaID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    OperationVersionIDParam.Value = operationVersionID;
                    ElementNameParam.Value = part.ElementName;
                    SchemaIDParam.Value = GetIDFromName(part.NamespaceURI, EntityClass.Schema, txn, conn);
                    if (((Guid)SchemaIDParam.Value) == Guid.Empty)
                    {
                        throw new ApplicationException(string.Format("Attempt to create message part with Element {0} in Namespace {1} as schema for this namespace does not exist in the Service Catalog", part.ElementName, part.NamespaceURI));
                    }
                    OrdinalParam.Value = ordinal;
                    IsRequestMsgParam.Value = isRequestMsg;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(OperationVersionIDParam);
                    cmd.Parameters.Add(ElementNameParam);
                    cmd.Parameters.Add(SchemaIDParam);
                    cmd.Parameters.Add(OrdinalParam);
                    cmd.Parameters.Add(IsRequestMsgParam);

                    cmd.ExecuteNonQuery();
                    ID = (Guid)IDParam.Value;
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        private static void CreateSoapFault(SoapFault fault, Guid operationVersionID, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateSoapFaultForOperationVersion]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter OperationVersionIDParam = new SqlParameter("@OperationVersionID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter ActionParam = new SqlParameter("@Action", SqlDbType.NVarChar, 256);
                    SqlParameter ProtectionLevelParam = new SqlParameter("@ProtectionLevel", SqlDbType.NVarChar, 32);
                    SqlParameter SchemaIDParam = new SqlParameter("@SchemaID", SqlDbType.UniqueIdentifier);
                    SqlParameter ElementNameParam = new SqlParameter("@ElementName", SqlDbType.NVarChar, 400);

                    cmd.CommandType = CommandType.StoredProcedure;

                    IDParam.Direction = ParameterDirection.Output;
                    OperationVersionIDParam.Value = operationVersionID;
                    NameParam.Value = fault.Name;
                    SchemaIDParam.Value = GetIDFromName(fault.NamespaceURI, EntityClass.Schema, txn, conn);
                    if (((Guid)SchemaIDParam.Value) == Guid.Empty)
                    {
                        throw new ApplicationException(string.Format("Attempt to create message fault with Namespace {0} as schema for this namespace does not exist in the Service Catalog", fault.NamespaceURI));
                    }
                    ActionParam.Value = fault.Action;
                    ProtectionLevelParam.Value = fault.ProtectionLevel.ToString();
                    ElementNameParam.Value = fault.ElementName;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(OperationVersionIDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(SchemaIDParam);
                    cmd.Parameters.Add(ActionParam);
                    cmd.Parameters.Add(ProtectionLevelParam);
                    cmd.Parameters.Add(ElementNameParam);

                    cmd.ExecuteNonQuery();
                    ID = (Guid)IDParam.Value;
                    fault.ID = ID;
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }



        public static OperationGroupList ListOperationGroups(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            OperationGroupList list = new OperationGroupList();
            const string sprocname = "[ServiceEngine].[ListOperationGroups]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {

                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.OperationGroups = new List<OperationGroupListElement>();
                            while (reader.Read())
                            {
                                OperationGroupListElement element = new OperationGroupListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.OperationGroups.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;
        }

        public static OperationGroupList ListOperationGroupsByOperation(Guid operationID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            OperationGroupList list = new OperationGroupList();
            const string sprocname = "[ServiceEngine].[ListOperationGroupsByOperation]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@operationID", SqlDbType.UniqueIdentifier);
                    IDParam.Value = operationID;
                    cmd.Parameters.Add(IDParam);

                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.OperationGroups = new List<OperationGroupListElement>();
                            while (reader.Read())
                            {
                                OperationGroupListElement element = new OperationGroupListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.OperationGroups.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;
        }

        public static OperationList ListOperations(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            OperationList list = new OperationList();
            const string sprocname = "[ServiceEngine].[ListOperations]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {

                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Operations = new List<OperationListElement>();
                            while (reader.Read())
                            {
                                OperationListElement element = new OperationListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Operations.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;

        }

        public static void DeleteOperationGroup(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteOperationGroup]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;

                    NameParam.Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);

                    cmd.ExecuteNonQuery();

                    string operationGroupName = (string)NameParam.Value;
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        public static void DeleteOperation(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteOperation]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }


                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;

                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        public static void DeleteOperationVersion(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteOperationVersion]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

                // Delete all Soap Faults associations for this version
                DeleteSoapFaults(ID, txn, conn);

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        public static OperationGroup UpdateOperationGroup(OperationGroup operationGroup, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateOperationGroup]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                if (txn == null) txn = conn.BeginTransaction(IsolationLevel.Snapshot);

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;

                    IDParam.Value = operationGroup.ID;
                    NameParam.Value = operationGroup.Name;
                    DescriptionParam.Value = operationGroup.Description;


                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);

                    cmd.ExecuteNonQuery();
                }
                //get existing versions

                List<OperationListElement> oldList = ListOperationsByGroup(operationGroup.ID, txn, conn);
                /// Delete all items from the old list
                if (oldList != null)
                {
                    foreach (OperationListElement oldListElement in oldList)
                    {
                        RemoveOperationFromGroup(operationGroup.ID, oldListElement.ID, txn, conn);
                    }
                }
                /// Add all items from the new list
                if (operationGroup.Operations != null)
                {
                    foreach (OperationListElement newListElement in operationGroup.Operations)
                    {
                        AssignOperationToGroup(operationGroup.ID, newListElement.ID, txn, conn);
                    }
                }
                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return operationGroup;
        }


        public static Operation UpdateOperation(Operation operation, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateOperation]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                if (txn == null) txn = conn.BeginTransaction(IsolationLevel.Snapshot);

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;

                    IDParam.Value = operation.ID;
                    NameParam.Value = operation.Name;
                    DescriptionParam.Value = operation.Description;

                    if (operation.Policy == null)
                    {
                        PolicyIDParam.Value = DBNull.Value;
                    }
                    else
                    {
                        PolicyIDParam.Value = operation.Policy.ID;
                    }

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(PolicyIDParam);

                    cmd.ExecuteNonQuery();
                    ID = (Guid)IDParam.Value;

                }
                //get existing versions

                OperationVersionList list = ListOperationVersions(ID, txn, conn);

                if (list.OperationVersions != null)
                {
                    bool present = false;
                    if (list.OperationVersions.Count > operation.Versions.Count)
                    {
                        foreach (OperationVersionListElement version in list.OperationVersions)
                        {
                            present = false;
                            foreach (OperationVersion xversion in operation.Versions)
                            {
                                if (version.ID == xversion.ID)
                                    present = true;
                            }
                            if (!present)
                                DeleteOperationVersion(version.ID, txn, conn);

                        }
                    }
                    foreach (OperationVersion xversion in operation.Versions)
                    {
                        present = false;
                        foreach (OperationVersionListElement version in list.OperationVersions)
                        {
                            if (version.ID == xversion.ID)
                            {
                                UpdateOperationVersion(xversion, txn, conn);
                                present = true;
                                break;
                            }
                        }
                        if (!present)
                            CreateOperationVersion(xversion, txn, conn);
                    }

                }
                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return operation;
        }

        public static OperationVersion UpdateOperationVersion(OperationVersion operationVersion, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            Guid policyID = Guid.Empty;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateOperationVersion]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                if (txn == null) txn = conn.BeginTransaction(IsolationLevel.Snapshot);

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter VersionMajorParam = new SqlParameter("@VersionMajor", SqlDbType.Int);
                    SqlParameter VersionMinorParam = new SqlParameter("@VersionMinor", SqlDbType.Int);
                    SqlParameter VersionBuildParam = new SqlParameter("@VersionBuild", SqlDbType.Int);
                    SqlParameter VersionQFEParam = new SqlParameter("@VersionQFE", SqlDbType.Int);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter AutoResponseParam = new SqlParameter("@AutoResponse", SqlDbType.Xml);
                    SqlParameter IsPublicParam = new SqlParameter("@IsPublic", SqlDbType.Bit);
                    SqlParameter IsActiveParam = new SqlParameter("@IsActive", SqlDbType.Bit);
                    SqlParameter IsTestableParam = new SqlParameter("@IsTestable", SqlDbType.Bit);
                    SqlParameter RequestTransformParam = new SqlParameter("@RequestTransform", SqlDbType.Xml);
                    SqlParameter ResponseTransformParam = new SqlParameter("@ResponseTransform", SqlDbType.Xml);
                    SqlParameter ChannelIDParam = new SqlParameter("@ChannelID", SqlDbType.UniqueIdentifier);
                    SqlParameter MonikerParam = new SqlParameter("@Moniker", SqlDbType.Xml);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = operationVersion.ID;
                    VersionMajorParam.Value = int.Parse(operationVersion.Version.Major);
                    VersionMinorParam.Value = int.Parse(operationVersion.Version.Minor);
                    VersionBuildParam.Value = int.Parse(operationVersion.Version.Build);
                    VersionQFEParam.Value = int.Parse(operationVersion.Version.Qfe);
                    DescriptionParam.Value = operationVersion.Description;
                    if (operationVersion.AutoResponse != null)
                    {
                        AutoResponseParam.Value = operationVersion.AutoResponse.OuterXml;
                    }
                    else
                    {
                        AutoResponseParam.Value = DBNull.Value;
                    }
                    IsPublicParam.Value = operationVersion.IsPublic;
                    IsActiveParam.Value = operationVersion.IsActive;
                    IsTestableParam.Value = operationVersion.IsTestable;
                    if (operationVersion.RequestTransformXSLT != null)
                    {
                        RequestTransformParam.Value = operationVersion.RequestTransformXSLT.OuterXml;
                    }
                    else
                    {
                        RequestTransformParam.Value = DBNull.Value;
                    }
                    if (operationVersion.ResponseTransformXSLT != null)
                    {
                        ResponseTransformParam.Value = operationVersion.ResponseTransformXSLT.OuterXml;
                    }
                    else
                    {
                        ResponseTransformParam.Value = DBNull.Value;
                    }
                    if (operationVersion.Channel != null)
                    {
                        if (operationVersion.Channel.ID.Equals(Guid.Empty))
                        {
                            ChannelIDParam.Value = CreateChannel(operationVersion.Channel, txn, conn);
                        }
                        else
                        {
                            ChannelIDParam.Value = operationVersion.Channel.ID;
                        }
                    }
                    else
                    {
                        ChannelIDParam.Value = null;
                        //throw new ApplicationException("CreateOperationVersion requires ChannelType, but no ChannelType was supplied");
                    }
                    if (operationVersion.Moniker != null)
                    {
                        MonikerParam.Value = operationVersion.Moniker.OuterXml;
                    }
                    else
                    {
                        MonikerParam.Value = null;
                        //throw new ApplicationException("CreateOperationVersion failed: Moniker is a required element but was not sent");
                    }

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(VersionMajorParam);
                    cmd.Parameters.Add(VersionMinorParam);
                    cmd.Parameters.Add(VersionBuildParam);
                    cmd.Parameters.Add(VersionQFEParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(AutoResponseParam);
                    cmd.Parameters.Add(IsPublicParam);
                    cmd.Parameters.Add(IsActiveParam);
                    cmd.Parameters.Add(IsTestableParam);
                    cmd.Parameters.Add(RequestTransformParam);
                    cmd.Parameters.Add(ResponseTransformParam);
                    cmd.Parameters.Add(ChannelIDParam);
                    cmd.Parameters.Add(MonikerParam);

                    cmd.ExecuteNonQuery();
                    ID = (Guid)IDParam.Value;

                    int partNo = 0;

                    DeleteMessagesFromOperationVersion(ID, txn, conn);

                    if (operationVersion.RequestMsg != null)
                    {
                        foreach (MessagePart part in operationVersion.RequestMsg)
                        {
                            CreateMessagePart(part, ID, partNo++, SC.IsRequestMsg, txn, conn);
                        }
                    }

                    if (operationVersion.ResponseMsg != null)
                    {
                        partNo = 0;
                        foreach (MessagePart part in operationVersion.ResponseMsg)
                        {
                            CreateMessagePart(part, ID, partNo++, SC.IsResponseMsg, txn, conn);
                        }
                    }

                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return operationVersion;
        }

        private static void DeleteVersionsFromOperation(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteVersionsFromOperation]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@OperationID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        private static void DeleteMessagesFromOperationVersion(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteMessagesFromOperationVersion]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        private static void DeleteSoapFaults(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteSoapFaultsForOperationVersion]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@OperationVersionID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        private static void DeleteSoapFault(Guid ID, Guid soapFaultID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteSoapFaultForOperationVersion]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter FaultIDParam = new SqlParameter("@SoapFaultID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    FaultIDParam.Value = soapFaultID;
                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(FaultIDParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        public static void AssignAssociatedOperationVersionToEndpoint(Guid operationVersionID, Guid endpointID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[AssignAssociatedOperationVersionToEndpoint]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter OperationVersionIDParam = new SqlParameter("@OperationVersionID", SqlDbType.UniqueIdentifier);
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    OperationVersionIDParam.Value = operationVersionID;
                    cmd.Parameters.Add(OperationVersionIDParam);
                    EndpointIDParam.Value = endpointID;
                    cmd.Parameters.Add(EndpointIDParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        public static void RemoveAssociatedOperationVersionFromEndpoint(Guid operationVersionID, Guid endpointID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemoveAssociatedOperationFromEndpoint]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter OperationVersionIDParam = new SqlParameter("@OperationVersionID", SqlDbType.UniqueIdentifier);
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    OperationVersionIDParam.Value = operationVersionID;
                    cmd.Parameters.Add(OperationVersionIDParam);
                    EndpointIDParam.Value = endpointID;
                    cmd.Parameters.Add(EndpointIDParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        public static void AssignPublishedOperationVersionToEndpoint(Guid operationVersionID, Guid endpointID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[AssignPublishedOperationVersionToEndpoint]";
            const string sprocname2 = "[ServiceEngine].[ListRuntimeServersForEndpointID]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter OperationVersionIDParam = new SqlParameter("@OperationVersionID", SqlDbType.UniqueIdentifier);
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    OperationVersionIDParam.Value = operationVersionID;
                    cmd.Parameters.Add(OperationVersionIDParam);
                    EndpointIDParam.Value = endpointID;
                    cmd.Parameters.Add(EndpointIDParam);

                    cmd.ExecuteNonQuery();
                }

                List<Guid> runtimeServerIDs = new List<Guid>();
                using (SqlCommand cmd = new SqlCommand(sprocname2, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = endpointID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                runtimeServerIDs.Add(reader.GetGuid(0));
                            }
                        }
                    }
                }

                // Update any UDDI associations 
                if (runtimeServerIDs.Count > 0)
                {
                    foreach (Guid runtimeServerID in runtimeServerIDs)
                    {
                        PublishEndPointToUddi(endpointID, runtimeServerID, false, txn, conn);
                    }
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        public static void RemovePublishedOperationVersionFromEndpoint(Guid operationVersionID, Guid endpointID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemovePublishedOperationFromEndpoint]";
            const string sprocname2 = "[ServiceEngine].[ListRuntimeServersForEndpointID]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter OperationVersionIDParam = new SqlParameter("@OperationVersionID", SqlDbType.UniqueIdentifier);
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    OperationVersionIDParam.Value = operationVersionID;
                    cmd.Parameters.Add(OperationVersionIDParam);
                    EndpointIDParam.Value = endpointID;
                    cmd.Parameters.Add(EndpointIDParam);

                    cmd.ExecuteNonQuery();
                }

                OperationVersionList pubOpVersionsList = ListPublishedOperationsForEndpoint(endpointID, txn, conn);
                if (pubOpVersionsList == null ||
                    pubOpVersionsList.OperationVersions == null ||
                    pubOpVersionsList.OperationVersions.Count == 0)
                {
                    List<Guid> runtimeServerIDs = new List<Guid>();
                    // Get all the Runtimeservers ssociated with this EndPoint
                    using (SqlCommand cmd = new SqlCommand(sprocname2, conn, txn))
                    {
                        SqlParameter IDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);

                        cmd.CommandType = CommandType.StoredProcedure;
                        IDParam.Value = endpointID;
                        cmd.Parameters.Add(IDParam);

                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                while (reader.Read())
                                {
                                    runtimeServerIDs.Add(reader.GetGuid(0));
                                }
                            }
                        }
                    }

                    // Remove any UDDI associations 
                    if (runtimeServerIDs.Count > 0)
                    {
                        foreach (Guid runtimeServerID in runtimeServerIDs)
                        {
                            RemoveEndPointFromUddi(endpointID, runtimeServerID, txn, conn);
                        }
                    }
                }
                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }


        public static PolicyList ListPolicies(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            PolicyList list = new PolicyList();
            const string sprocname = "[ServiceEngine].[ListPolicies]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Policies = new List<PolicyListElement>();
                            while (reader.Read())
                            {
                                PolicyListElement element = new PolicyListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.Policies.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;

        }

        public static Guid CreatePolicy(Policy policy, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreatePolicy]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter PolicyParam = new SqlParameter("@Policy", SqlDbType.Xml);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    NameParam.Value = policy.Name;
                    if (policy.WSPolicy != null)
                    {
                        PolicyParam.Value = policy.WSPolicy.OuterXml;
                    }
                    else
                    {
                        throw new ApplicationException("CreatePolicy failed: WSPolicy is a required element but was not sent");
                    }
                    DescriptionParam.Value = policy.Description;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(PolicyParam);
                    cmd.Parameters.Add(DescriptionParam);

                    cmd.ExecuteNonQuery();
                    ID = (Guid)IDParam.Value;
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return ID;
        }


        public static void DeletePolicy(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeletePolicy]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        public static Policy UpdatePolicy(Policy policy, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdatePolicy]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter PolicyParam = new SqlParameter("@Policy", SqlDbType.Xml);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = policy.ID;
                    NameParam.Value = policy.Name;
                    if (policy.WSPolicy != null)
                    {
                        PolicyParam.Value = policy.WSPolicy.OuterXml;
                    }
                    else
                    {
                        throw new ApplicationException("UpdatePolicy failed: WSPolicy is a required element but was not sent");
                    }
                    DescriptionParam.Value = policy.Description;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(PolicyParam);
                    cmd.Parameters.Add(DescriptionParam);

                    cmd.ExecuteNonQuery();
                }
                if ((autoclose) && (txn != null)) txn.Commit();

            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return policy;
        }

        public static void DeleteMSEBinding(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteBinding]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        public static MSEBindingList ListMSEBindings(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            MSEBindingList list = new MSEBindingList();
            const string sprocname = "[ServiceEngine].[ListBindings]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.MSEBindings = new List<MSEBindingListElement>();
                            while (reader.Read())
                            {
                                MSEBindingListElement element = new MSEBindingListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.MSEBindings.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return list;

        }

        public static MSEBinding UpdateMSEBinding(MSEBinding binding, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateBinding]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter BindingConfigParam = new SqlParameter("@BindingConfig", SqlDbType.Xml);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = binding.ID;
                    NameParam.Value = binding.Name;
                    if (binding.BindingConfig != null)
                    {
                        BindingConfigParam.Value = binding.BindingConfig.OuterXml;
                    }
                    else
                    {
                        throw new ApplicationException("UpdateBinding failed: BindingConfig is a required element but was not sent");
                    }
                    if (binding.Description != null)
                    {
                        DescriptionParam.Value = binding.Description;
                    }
                    else
                    {
                        DescriptionParam.Value = DBNull.Value;
                    }

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(BindingConfigParam);
                    cmd.Parameters.Add(DescriptionParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return binding;
        }

        public static Guid CreateMSEBinding(MSEBinding binding, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateBinding]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter BindingConfigParam = new SqlParameter("@BindingConfig", SqlDbType.Xml);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    NameParam.Value = binding.Name;
                    if (binding.BindingConfig != null)
                    {
                        BindingConfigParam.Value = binding.BindingConfig.OuterXml;
                    }
                    else
                    {
                        throw new ApplicationException("CreateBinding failed: BindingConfig is a required element but was not sent");
                    }
                    if (binding.Description != null)
                    {
                        DescriptionParam.Value = binding.Description;
                    }
                    else
                    {
                        DescriptionParam.Value = DBNull.Value;
                    }

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(BindingConfigParam);
                    cmd.Parameters.Add(DescriptionParam);

                    cmd.ExecuteNonQuery();

                    ID = (Guid)IDParam.Value;
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }

            return ID;
        }

        public static List<OperationVersion> GetOperationVersionsForEndpointAndAction(Guid endpointID, string action, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<OperationVersion> response = new List<OperationVersion>();
            const string sprocname = "[ServiceEngine].[GetOperationVersionsForEndpointAndAction]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);
                    SqlParameter OperationNameParam = new SqlParameter("@OperationName", SqlDbType.NVarChar, 4000);
                    SqlParameter VersionMajorParam = new SqlParameter("@VersionMajor", SqlDbType.Int);
                    SqlParameter VersionMinorParam = new SqlParameter("@VersionMinor", SqlDbType.Int);
                    SqlParameter VersionBuildParam = new SqlParameter("@VersionBuild", SqlDbType.Int);
                    SqlParameter VersionQFEParam = new SqlParameter("@VersionQFE", SqlDbType.Int);

                    cmd.CommandType = CommandType.StoredProcedure;
                    EndpointIDParam.Value = endpointID;
                    OperationNameParam.Value = DBNull.Value;
                    VersionMajorParam.Value = DBNull.Value;
                    VersionMinorParam.Value = DBNull.Value;
                    VersionBuildParam.Value = DBNull.Value;
                    VersionQFEParam.Value = DBNull.Value;

                    // Parse Action into subfields
                    if (!string.IsNullOrEmpty(action))
                    {
                        string[] tokens = action.Split(new char[] { ':' });
                        if (tokens.Length > 2) throw new ApplicationException(string.Format("GetOperationVersionsForEndpointAndAction failed to parse Action [{0}].  Expected no more than two sections, found [{1}]", action, tokens.Length));
                        if (tokens.Length == 1)
                        { // Endpoint and Operation Name
                            OperationNameParam.Value = tokens[0];
                            VersionMajorParam.Value = DBNull.Value;
                            VersionMinorParam.Value = DBNull.Value;
                            VersionBuildParam.Value = DBNull.Value;
                            VersionQFEParam.Value = DBNull.Value;
                        }
                        else
                        {
                            if (tokens.Length == 2)
                            { // Endpoint, Operation Name and Version
                                OperationNameParam.Value = tokens[0];
                                SetVersionParams(tokens[1], VersionMajorParam, VersionMinorParam, VersionBuildParam, VersionQFEParam);
                            }
                        }
                    }

                    cmd.Parameters.Add(EndpointIDParam);
                    cmd.Parameters.Add(OperationNameParam);
                    cmd.Parameters.Add(VersionMajorParam);
                    cmd.Parameters.Add(VersionMinorParam);
                    cmd.Parameters.Add(VersionBuildParam);
                    cmd.Parameters.Add(VersionQFEParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            OperationVersion version = new OperationVersion();

                            version.ID = reader.GetGuid(0);
                            VersionNumber verNumber = new VersionNumber();
                            verNumber.Major = reader.GetInt32(1).ToString();
                            verNumber.Minor = reader.GetInt32(2).ToString();
                            verNumber.Build = reader.GetInt32(3).ToString();
                            verNumber.Qfe = reader.GetInt32(4).ToString();
                            version.Version = verNumber;
                            if (reader.IsDBNull(5))
                            {
                                version.AutoResponse = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(5).CreateReader());
                                version.AutoResponse = xdoc.DocumentElement;

                            }
                            //rcamacho - version.OperationName was missing
                            version.OperationName = OperationNameParam.Value.ToString();
                            version.IsTestable = reader.GetBoolean(6);
                            version.IsActive = reader.GetBoolean(7);
                            version.RequestMsg = GetMessage(version.ID, SC.IsRequestMsg, txn, conn);
                            version.ResponseMsg = GetMessage(version.ID, SC.IsResponseMsg, txn, conn);

                            if (reader.IsDBNull(8))
                            {
                                version.RequestTransformXSLT = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(8).CreateReader());
                                version.RequestTransformXSLT = xdoc.DocumentElement;
                            }

                            if (reader.IsDBNull(9))
                            {
                                version.ResponseTransformXSLT = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(9).CreateReader());
                                version.ResponseTransformXSLT = xdoc.DocumentElement;
                            }

                            if (reader.IsDBNull(10))
                            {
                                version.Channel = null;
                            }
                            else
                            {
                                Channel chnl = GetChannel(reader.GetGuid(10), txn, conn);
                                version.Channel = chnl;
                            }

                            if (reader.IsDBNull(11))
                            {
                                version.Moniker = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(11).CreateReader());
                                version.Moniker = xdoc.DocumentElement;
                            }
                            version.OperationID = reader.GetGuid(12);
                            // Get SoapFaults
                            version.SoapFaults = GetSoapFaults(version.ID, txn, conn);
                            response.Add(version);
                        }
                    }
                }
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            if (response.Count == 0)
            {
                throw new ApplicationException(string.Format("No Active Operations exist on this endpoint that are identified by the Action parameter {0}", action));
            }

            return response;

        }

        private static void SetVersionParams(string versionString, SqlParameter VersionMajorParam, SqlParameter VersionMinorParam, SqlParameter VersionBuildParam, SqlParameter VersionQFEParam)
        {
            string[] versionNodes = versionString.Split(new char[] { '.' });

            try
            {
                if (versionNodes.Length > 0) VersionMajorParam.Value = int.Parse(versionNodes[0]);
                if (versionNodes.Length > 1) VersionMinorParam.Value = int.Parse(versionNodes[1]);
                if (versionNodes.Length > 2) VersionBuildParam.Value = int.Parse(versionNodes[2]);
                if (versionNodes.Length > 3) VersionQFEParam.Value = int.Parse(versionNodes[3]);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Attempt to parse version number from Action failed: " + ex.Message);
            }
        }

        public static List<OperationVersion> GetOperationVersionsForEndpointAndFirstBodyElement(Guid endpointID, string elementName, string namespaceURI, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<OperationVersion> response = new List<OperationVersion>();
            const string sprocname = "[ServiceEngine].[GetOperationVersionsForEndpointAndFirstBodyElement]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);
                    SqlParameter ElementNameParam = new SqlParameter("@ElementName", SqlDbType.NVarChar, 400);
                    SqlParameter namespaceURIParam = new SqlParameter("@namespaceURI", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;
                    EndpointIDParam.Value = endpointID;
                    ElementNameParam.Value = elementName;
                    namespaceURIParam.Value = namespaceURI;

                    cmd.Parameters.Add(EndpointIDParam);
                    cmd.Parameters.Add(ElementNameParam);
                    cmd.Parameters.Add(namespaceURIParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            OperationVersion version = new OperationVersion();
                            version.ID = reader.GetGuid(0);
                            VersionNumber verNumber = new VersionNumber();
                            verNumber.Major = reader.GetInt32(1).ToString();
                            verNumber.Minor = reader.GetInt32(2).ToString();
                            verNumber.Build = reader.GetInt32(3).ToString();
                            verNumber.Qfe = reader.GetInt32(4).ToString();
                            version.Version = verNumber;
                            if (reader.IsDBNull(5))
                            {
                                version.AutoResponse = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(5).CreateReader());
                                version.AutoResponse = xdoc.DocumentElement;

                            }
                            version.IsTestable = reader.GetBoolean(6);
                            version.IsActive = reader.GetBoolean(7);
                            version.RequestMsg = GetMessage(version.ID, SC.IsRequestMsg, txn, conn);
                            version.ResponseMsg = GetMessage(version.ID, SC.IsResponseMsg, txn, conn);

                            if (reader.IsDBNull(8))
                            {
                                version.RequestTransformXSLT = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(8).CreateReader());
                                version.RequestTransformXSLT = xdoc.DocumentElement;
                            }

                            if (reader.IsDBNull(9))
                            {
                                version.ResponseTransformXSLT = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(9).CreateReader());
                                version.ResponseTransformXSLT = xdoc.DocumentElement;
                            }

                            if (reader.IsDBNull(10))
                            {
                                version.Channel = null;
                            }
                            else
                            {
                                Channel chnl = GetChannel(reader.GetGuid(10), txn, conn);
                                version.Channel = chnl;
                            }

                            if (reader.IsDBNull(11))
                            {
                                version.Moniker = null;
                            }
                            else
                            {
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(11).CreateReader());
                                version.Moniker = xdoc.DocumentElement;
                            }
                            version.OperationID = reader.GetGuid(12);
                            // Get SoapFaults
                            version.SoapFaults = GetSoapFaults(version.ID, txn, conn);
                            response.Add(version);
                        }
                    }
                }
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            if (response.Count == 0)
            {
                throw new ApplicationException(string.Format("No Active Operations exist on this endpoint with the first body element of {0} in namespace {1}", elementName, namespaceURI));
            }

            return response;

        }

        public static void PublishRuntimeServerEndPointsToUddi(Guid runtimeServerID, bool refresh, SqlTransaction txn, SqlConnection conn)
        {
            if (Configurator.svcConfig.serviceCatalogUddi == null ||
                Configurator.svcConfig.serviceCatalogUddi.UddiIntegrationMode == false)
                return;

            RuntimeServer runtimeServer = GetRuntimeServer(runtimeServerID, false, txn, conn);

            if (runtimeServer.Endpoints != null && runtimeServer.Endpoints.Count > 0)
            {
                foreach (Endpoint endpint in runtimeServer.Endpoints)
                {
                    PublishEndPointToUddi(endpint, runtimeServer, refresh, txn, conn);
                }
            }
        }

        public static void RemoveRuntimeServerEndPointsFromUddi(Guid runtimeServerID, SqlTransaction txn, SqlConnection conn)
        {
            if (Configurator.svcConfig.serviceCatalogUddi == null ||
                Configurator.svcConfig.serviceCatalogUddi.UddiIntegrationMode == false)
                return;

            RuntimeServer runtimeServer = GetRuntimeServer(runtimeServerID, false, txn, conn);

            if (runtimeServer.Endpoints != null && runtimeServer.Endpoints.Count > 0)
            {
                foreach (Endpoint endpint in runtimeServer.Endpoints)
                {
                    RemoveEndPointFromUddi(endpint,runtimeServer, txn, conn);
                }
            }
        }

        private static void PublishEndPointToUddi(Endpoint endpoint, RuntimeServer runtimeServer, bool refresh, SqlTransaction txn, SqlConnection conn)
        {
            if (Configurator.svcConfig.serviceCatalogUddi == null ||
                Configurator.svcConfig.serviceCatalogUddi.UddiIntegrationMode == false)
                return;

            bool autoclose = false;

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                if (refresh == true)
                {
                    RemoveEndPointFromUddi(endpoint, runtimeServer, txn, conn);
                }

                // Check if the Endpoint is valid for publishing
                if (endpoint.PubOpVersionsList != null &&
                    endpoint.PubOpVersionsList.OperationVersions != null &&
                    endpoint.PubOpVersionsList.OperationVersions.Count > 0 )
                {
                    bool isUpdate = false;
                    bool isNew = false;

                    EndpointAssociatedUddi endpointAssociatedUddi = GetUddiAssociation(endpoint, runtimeServer);
                    if (endpointAssociatedUddi != null
                            && String.IsNullOrEmpty(endpointAssociatedUddi.ServiceKey) == false
                            && String.IsNullOrEmpty(endpointAssociatedUddi.BindingKey) == false
                            && String.IsNullOrEmpty(endpointAssociatedUddi.TModelKey) == false)
                    {
                        // Update only if anything has changed
                        Uri serviceUri = GetEndpointServiceUri(endpoint, runtimeServer);
                        if (string.Compare(serviceUri.ToString(), endpointAssociatedUddi.BindingUrl, true) != 0)
                        {
                            isUpdate = true;
                        }
                    }
                    else
                    {
                        isNew = true;
                    }
                    if (isUpdate || isNew)
                    {
                        // Publish the Endpoint to UDDI
                        using (UddiPublisher uddiPublisher = new UddiPublisher(Configurator.svcConfig.serviceCatalogUddi))
                        {
                            if (isUpdate)
                            {
                                endpoint = uddiPublisher.UpdateEndpointToUddi(endpoint,runtimeServer);
                            }
                            if (isNew)
                            {
                                endpoint = uddiPublisher.PublishEndpointToUddi(endpoint, runtimeServer);
                            }
                        }
                        // Get the newly created or updates association
                        endpointAssociatedUddi = GetUddiAssociation(endpoint, runtimeServer);
                        string procName = "";
                        if (isUpdate)
                            procName = "[ServiceEngine].[UpdateEndpointUddiAssociation]";
                        else
                            procName = "[ServiceEngine].[CreateEndpointUddiAssociation]";
                        // Add the association to database
                        using (SqlCommand cmd = new SqlCommand(procName, conn, txn))
                        {
                            SqlParameter ServiceKeyParam = null;
                            SqlParameter TModelKeyParam = null;
                            SqlParameter BindingKeyParam = null;
                            if (isUpdate == false)
                            {
                                ServiceKeyParam = new SqlParameter("@ServiceKey", SqlDbType.NVarChar, 64);
                                TModelKeyParam = new SqlParameter("@TModelKey", SqlDbType.NVarChar, 64);
                                BindingKeyParam = new SqlParameter("@BindingKey", SqlDbType.NVarChar, 64);
                            }
                            SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);
                            SqlParameter RuntimeServerIDParam = new SqlParameter("@RuntimeServerID", SqlDbType.UniqueIdentifier);
                            SqlParameter WsdlUrlParam = new SqlParameter("@WsdlUrl", SqlDbType.NVarChar, 400);
                            SqlParameter BindingUrlParam = new SqlParameter("@BindingUrl", SqlDbType.NVarChar, 400);

                            cmd.CommandType = CommandType.StoredProcedure;

                            if (isUpdate == false)
                            {
                                ServiceKeyParam.Value = endpointAssociatedUddi.ServiceKey;
                                TModelKeyParam.Value = endpointAssociatedUddi.TModelKey;
                                BindingKeyParam.Value = endpointAssociatedUddi.BindingKey;
                            }
                            EndpointIDParam.Value = endpoint.ID;
                            RuntimeServerIDParam.Value = runtimeServer.ID;
                            WsdlUrlParam.Value = endpointAssociatedUddi.WsdlUrl;
                            BindingUrlParam.Value = endpointAssociatedUddi.BindingUrl;

                            if (isUpdate == false)
                            {
                                cmd.Parameters.Add(ServiceKeyParam);
                                cmd.Parameters.Add(TModelKeyParam);
                                cmd.Parameters.Add(BindingKeyParam);
                            }
                            cmd.Parameters.Add(EndpointIDParam);
                            cmd.Parameters.Add(RuntimeServerIDParam);
                            cmd.Parameters.Add(WsdlUrlParam);
                            cmd.Parameters.Add(BindingUrlParam);

                            cmd.ExecuteNonQuery();
                        }
                    }
                }
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }
        }


        private static void PublishEndPointToUddi(Guid endpointID, Guid runtimeServerID, bool refresh, SqlTransaction txn, SqlConnection conn)
        {
            if (Configurator.svcConfig.serviceCatalogUddi == null ||
                Configurator.svcConfig.serviceCatalogUddi.UddiIntegrationMode == false)
                return;

            bool autoclose = false;

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                Endpoint endPoint = GetEndpoint(endpointID, runtimeServerID, false, txn, conn);
                RuntimeServer runtimeServer = GetRuntimeServer(runtimeServerID, false, txn, conn);
                PublishEndPointToUddi(endPoint, runtimeServer, refresh, txn, conn);
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }
        }


        private static void RemoveEndPointFromUddi(Endpoint endpoint, RuntimeServer runtimeServer, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemoveEndpointUddiAssociation]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                // Remove the Endpoint from UDDI
                if (Configurator.svcConfig.serviceCatalogUddi != null &&
                    Configurator.svcConfig.serviceCatalogUddi.UddiIntegrationMode == true)
                {
                    using (UddiPublisher uddiPublisher = new UddiPublisher(Configurator.svcConfig.serviceCatalogUddi))
                    {
                        uddiPublisher.RemoveEndpointFromUddi(endpoint,runtimeServer );
                    }
                }
                // Remove the Endpoint association from Database
                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter EndpointIDParam = new SqlParameter("@EndpointID", SqlDbType.UniqueIdentifier);
                    SqlParameter RuntimeServerIDParam = new SqlParameter("@RuntimeServerID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    EndpointIDParam.Value = endpoint.ID;
                    RuntimeServerIDParam.Value = runtimeServer.ID; 

                    cmd.Parameters.Add(EndpointIDParam);
                    cmd.Parameters.Add(RuntimeServerIDParam);
                    cmd.ExecuteNonQuery();

                }
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }
        }

        private static void RemoveEndPointFromUddi(Guid endpointID, Guid runtimeServerID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemoveEndpointUddiAssociation]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                Endpoint endPoint = GetEndpoint(endpointID, runtimeServerID, false, txn, conn);
                RuntimeServer runtimeServer = GetRuntimeServer(runtimeServerID, false, txn, conn);
                RemoveEndPointFromUddi(endPoint, runtimeServer, txn, conn);
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }
        }

        internal static EndpointAssociatedUddi GetUddiAssociation(Endpoint endPoint, RuntimeServer runtimeServer)
        {
            EndpointAssociatedUddi endpointAssociatedUddi = null;

            if (endPoint.UddiAssociations != null && endPoint.UddiAssociations.Count > 0)
            {
                foreach (EndpointAssociatedUddi eua in endPoint.UddiAssociations)
                {
                    if (eua.EndpointID.CompareTo(endPoint.ID) == 0 &&
                        eua.RuntimeServerID.CompareTo(runtimeServer.ID) == 0)
                    {
                        endpointAssociatedUddi = eua;
                        break;
                    }
                }
            }
            return endpointAssociatedUddi;
        }

        private static string[] BuildOperationNameArray(List<OperationListElement> operations)
        {
            string[] operationNames = null;
            if (operations.Count > 0)
            {
                operationNames = new string[operations.Count];
                int counter = 0;
                foreach (OperationListElement operation in operations)
                {
                    operationNames[counter] = operation.Name;
                    counter++;
                }
            }
            return operationNames;
        }
        public static string GetExceptionDetails(Exception ex)
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("Exception Information Type[{0}] Message[{1}] ", ex.GetType().Name, ex.Message);
            Exception iex = ex.InnerException;
            while (iex != null)
            {
                sb.AppendFormat("Inner Exception Information Type[{0}] Message[{1}] ", iex.GetType().Name, iex.Message);
                iex = iex.InnerException;
            }
            return sb.ToString();
        }

        private static string RemoveUriScheme(string endPointUri)
        {
            if (endPointUri.IndexOf("://") > -1)
            {
                endPointUri = endPointUri.Substring(endPointUri.IndexOf("://") + 3);
            }
            return endPointUri;
        }

        public static void AppendImportElementToSchema(XmlDocument schema, XmlNode nxl)
        {
            bool found = false;
            XmlNamespaceManager xmlns = new XmlNamespaceManager(schema.NameTable);
            xmlns.AddNamespace("xsd", XmlSchema.Namespace);
            XmlNodeList importNodes = schema.SelectNodes("//xsd:import", xmlns);
            foreach (XmlNode xn in importNodes)
            {
                string name1 = null;
                string name2 = null;
                if (xn.Attributes["namespace"] != null) name1 = xn.Attributes["namespace"].Value;
                if (nxl.Attributes["namespace"] != null) name2 = nxl.Attributes["namespace"].Value;
                if ((!string.IsNullOrEmpty(name1)) && (!string.IsNullOrEmpty(name2)) && (name1.Equals(name2) == true))
                {
                    found = true;
                }
            }
            if (found == false)
            {
                // import does not exist so just add
                schema.FirstChild.InsertBefore(nxl, schema.FirstChild.FirstChild);
            }
        }

        //SGK
        public static void AssignEntityPolicy(Guid entityID, Guid policyID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[AssignEntityPolicy]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter EntityIDParam = new SqlParameter("@EntityID", SqlDbType.UniqueIdentifier);
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    EntityIDParam.Value = entityID;
                    cmd.Parameters.Add(EntityIDParam);
                    PolicyIDParam.Value = policyID;
                    cmd.Parameters.Add(PolicyIDParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        //SGK
        private static void DeletePoliciesFromEntity(Guid entityID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeletePoliciesFromEntity]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter EntityIDParam = new SqlParameter("@EntityID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    EntityIDParam.Value = entityID;
                    cmd.Parameters.Add(EntityIDParam);

                    cmd.ExecuteNonQuery();
                }

                if ((autoclose) && (txn != null)) txn.Commit();
            }
            catch (Exception)
            {
                if (txn != null) try { if (txn != null) txn.Rollback(); }
                    catch (InvalidOperationException) { };
                throw;
            }
            finally
            {
                if (autoclose) { try { conn.Close(); } catch (InvalidOperationException) { };}
            }
        }

        //SGK
        public static Policy[] GetEntityPolicies(Guid entityID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<Policy> polList = null;
            Policy[] arrPol = null;
            const string sprocname = "[ServiceEngine].[GetEntityPolicies]";

            try
            {
                if (conn == null) conn = new SqlConnection(Configurator.svcConfig.DBConnString);
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter EntityIDParam = new SqlParameter("@EntityID", SqlDbType.UniqueIdentifier);
                    EntityIDParam.Value = entityID;
                    cmd.Parameters.Add(EntityIDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            polList = new List<Policy>();
                            while (reader.Read())
                            {
                                polList.Add(GetPolicy(reader.GetGuid(0), txn, conn));
                            }
                        }
                    }
                }

                if (polList != null)
                {
                    arrPol = new Policy[polList.Count];
                    for (int i = 0; i < polList.Count; i++)
                    {
                        arrPol[i] = polList[i];
                    }
                }
            }
            finally
            {
                if (autoclose) { conn.Close(); }
            }

            return arrPol;
        }
    }
}

